Thread: Seeking Clarification on Function Definitions in PostgreSQL Extensions
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:
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
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
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 <ayushvatsa1810@gmail.com> wrote:
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
Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
From
"David G. Johnston"
Date:
On Tue, Jun 18, 2024 at 9:50 AM Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:
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.
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.
Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
From
"David G. Johnston"
Date:
On Tue, Jun 18, 2024 at 10:07 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
fun2 puzzles me. Why would you return parameters AND a single record (unless it's an error status).
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.
On Tue, Jun 18, 2024 at 1:16 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Jun 18, 2024 at 10:07 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:fun2 puzzles me. Why would you return parameters AND a single record (unless it's an error status).You mis-understand what 2 is doing. You should go re-read the docs for 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.
Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
From
"David G. Johnston"
Date:
On Tuesday, June 18, 2024, Ron Johnson <ronljohnsonjr@gmail.com> 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? Because the out parameters and the “record” represent the exact same thing.
David J.
On Tue, Jun 18, 2024 at 1:57 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, June 18, 2024, Ron Johnson <ronljohnsonjr@gmail.com> 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?
Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
From
"David G. Johnston"
Date:
On Tuesday, June 18, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Tue, Jun 18, 2024 at 1:57 PM David G. Johnston <david.g.johnston@gmail.com> wrote:On Tuesday, June 18, 2024, Ron Johnson <ronljohnsonjr@gmail.com> 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.
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.
Because the out parameters and the “record” represent the exact same thing.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.
On Tue, Jun 18, 2024 at 2:37 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, June 18, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:On Tue, Jun 18, 2024 at 1:57 PM David G. Johnston <david.g.johnston@gmail.com> wrote:On Tuesday, June 18, 2024, Ron Johnson <ronljohnsonjr@gmail.com> 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.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.
"A function returns a value*, but a procedure does not."
*In the case of SQL, "value" might be a set.
Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
From
"David G. Johnston"
Date:
On Tuesday, June 18, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Tue, Jun 18, 2024 at 2:37 PM David G. Johnston <david.g.johnston@gmail.com> wrote:On Tuesday, June 18, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:On Tue, Jun 18, 2024 at 1:57 PM David G. Johnston <david.g.johnston@gmail.com> wrote:On Tuesday, June 18, 2024, Ron Johnson <ronljohnsonjr@gmail.com> 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.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."A function returns a value*, but a procedure does not."*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.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, June 18, 2024, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>> 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.
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
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:
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;
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
Sorry but didn't get this shape part?
Thanks
Ayush Vatsa
Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
From
"David G. Johnston"
Date:
On Wednesday, June 19, 2024, Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:
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?
Sorry, predefined independently of the function. Using create type then referring to that type by name in the create function.
> Or a true record where the caller has to specify the shape.
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.