Subject: Seeking Clarification on Function Definitions in PostgreSQL Extensions

From: Ayush Vatsa <[email-masked]>
Date: Tue, 18 Jun 2024 22:20:10 +0530

Hi PostgreSQL community,
Recently I am exploring extensions in postgres and got a little confused
regarding the function definition present in SQL file. For example consider
below three functions:

CREATE FUNCTION fun1(integer)
RETURNS TABLE(
        col1 integer,
        col2 text
)
AS 'MODULE_PATHNAME', 'fun1'
LANGUAGE C;

CREATE FUNCTION fun2(
    IN  input integer,
    OUT col1 integer,
    OUT col2 text
)
RETURNS SETOF record
AS 'MODULE_PATHNAME', 'fun2'
LANGUAGE C;

CREATE FUNCTION fun3(
    IN  input integer,
    OUT col1 integer,
    OUT col2 text
)
AS 'MODULE_PATHNAME', 'fun3'
LANGUAGE C;

1/ I wanted to know what's the difference between the above three
definitions.
As per my understanding, "fun1" and "fun2" look the same, taking one
integer and returning two columns with multiple rows.
Whereas "fun3" is used when we are returning only one row, but my doubt for
"fun3" is that, Can the above definition (used for fun1 and fun2) cover
both single and multiple row scenarios.

2/ How does someone decide which type of definition is to be used?

Regards
Ayush Vatsa

---

From: Ron Johnson <[email-masked]>
Date: Tue, 18 Jun 2024 13:07:16 -0400

fun1 returns a table set just like any other SELECT statement.
fun2 puzzles me.  Why would you return parameters AND *a single record* (unless
it's an error status).
fun3 just returns two parameters.  Why isn't it a procedure?

fun2, returning parameters AND a function value, would have made my Comp
Sci professors very, very angry.  Definitely Bad Practice.

You choose which to use based on how much data you want to return.

On Tue, Jun 18, 2024 at 12:50 PM Ayush Vatsa <[email-masked]>
wrote:

> Whereas "fun3" is used when we are returning only one row, but my doubt
> for "fun3" is that, Can the above definition (used for fun1 and fun2) cover

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 18 Jun 2024 10:13:08 -0700

> integer and returning two columns with multiple rows.

Yes.

>
> Can the above definition (used for fun1 and fun2) cover both single and
> multiple row scenarios.
>

In so far as one is a valid number of rows to return from a function that
returns zero or more rows, yes.  But if the function is incapable of
returning more than one result it should not be marked with table/setof on
semantic grounds.

> 2/ How does someone decide which type of definition is to be used?
Between 1 and 2 is a style choice.  I prefer TABLE.  Using setof is more
useful when the returned type is predefined.  Or a true record where the
caller has to specify the shape.

For 3, having a non-set-returning-function that outputs multiple columns is
just odd, IMO.  Personally I'd go for pre-defining a composite type, then
return that type.

David J.

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 18 Jun 2024 10:15:28 -0700

> fun2 puzzles me.  Why would you return parameters AND *a single record* (unless

You mis-understand what 2 is doing.  You should go re-read the docs for
create function again.  Especially the description of rettype.

David J.

---

From: Ron Johnson <[email-masked]>
Date: Tue, 18 Jun 2024 13:54:37 -0400

> create function again.  Especially the description of rettype.

That's true.  I've even used "RETURNS SETOF record" before.

But I stand by returning OUT params and records at the same time.

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 18 Jun 2024 10:57:41 -0700

> 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?  Because the out parameters and the “record” represent
the exact same thing.

David J.

---

From: Ayush Vatsa <[email-masked]>
Date: Wed, 19 Jun 2024 22:16:44 +0530

Hi David,
Thanks for clarification
> I prefer TABLE.  Using setof is more useful when the returned type is
predefined
But in the table also isn't the returned type predefined? Example:
CREATE FUNCTION fun1(integer)
RETURNS TABLE(
        col1 integer,
        col2 text
)
AS 'MODULE_PATHNAME', 'fun1'
LANGUAGE C;
We know the returned type will have two columns with type -  integer and
text. Am I correct?

> Or a true record where the caller has to specify the shape.
Sorry but didn't get this shape part?

Thanks
Ayush Vatsa

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 19 Jun 2024 09:50:44 -0700

> We know the returned type will have two columns with type -  integer and

Sorry, predefined independently of the function.  Using create type then
referring to that type by name in the create function.

> Sorry but didn't get this shape part?

… from rec_func as (col1 isn’t, col2 text) …

The shape is two columns, type int and text respectively.

David J.