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:
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
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:
Show quoted text
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
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.
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.
Show quoted text
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.
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?
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.
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:
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
On Wednesday, June 19, 2024, Ayush Vatsa <ayushvatsa1810@gmail.com> wrote:
Hi David,
Thanks for clarificationI 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.