Subject: Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

From: Ron Johnson <[email-masked]>
Date: Tue, 18 Jun 2024 14:27:10 -0400

On Tue, Jun 18, 2024 at 1:57 PM David G. Johnston <
[email-masked]> wrote:

> On Tuesday, June 18, 2024, Ron Johnson <[email-masked]> wrote:
>
>>
>> But I stand by returning OUT params and records at the same time.
>>
>
> You mean you dislike adding the optional returns clause when output
> parameters exist?
>

Correct.  It breaks the distinction between function and procedure.

> Because the out parameters and the “record” represent the exact same thing.
>

What's the purpose?  Legacy of not having procedures?

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 18 Jun 2024 11:37:36 -0700

> Correct.  It breaks the distinction between function and procedure.

How so?

The two distinctions are functions can produce sets while procedures get
transaction control.

They both can produce a single multi-column output record.  The presence or
absence of the optional return clause on a function definition doesn’t
change that fact.

> What's the purpose?  Legacy of not having procedures?

So people can have a style guide that says always specify a returns clause
on function definitions.

David J.

---

From: Ron Johnson <[email-masked]>
Date: Tue, 18 Jun 2024 15:04:17 -0400

On Tue, Jun 18, 2024 at 2:37 PM David G. Johnston <
[email-masked]> wrote:

> They both can produce a single multi-column output record.  The presence
> or absence of the optional return clause on a function definition doesn’t
> change that fact.
>

"A function returns a value*, but a procedure does not."

*In the case of SQL, "value" might be a set.

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 18 Jun 2024 12:17:06 -0700

> *In the case of SQL, "value" might be a set.
Notably it’s the use of output arguments in create function that violate
the consistency, but using them is the only way to define an adhoc
multi-column result.

I’ll accept the narrow definition of “return value” being something that be
incorporated into an expression.  Procedures do not have that.  Hence they
don;y have a return clause.  Since the output arguments for a function are
return values specifying “returns record” just makes is perfectly clear
what is happening and that it is different than a procedure with the same
output arguments.

David J.

---

From: Tom Lane <[email-masked]>
Date: Tue, 18 Jun 2024 16:04:25 -0400

> So people can have a style guide that says always specify a returns clause

To my mind, the reason we allow RETURNS together with OUT parameter(s)
is so there's a place to write SETOF if you want that.

Yes, the RETURNS TABLE syntax is somewhat redundant with RETURNS
SETOF.  Blame the SQL standard for that.

			regards, tom lane