public inbox for [email protected]
help / color / mirror / Atom feedSeeking Clarification on Function Definitions in PostgreSQL Extensions
8+ messages / 3 participants
[nested] [flat]
* Seeking Clarification on Function Definitions in PostgreSQL Extensions
@ 2024-06-18 16:50 Ayush Vatsa <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Ayush Vatsa @ 2024-06-18 16:50 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
@ 2024-06-18 17:07 Ron Johnson <[email protected]>
parent: Ayush Vatsa <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Ron Johnson @ 2024-06-18 17:07 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
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 protected]>
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
@ 2024-06-18 17:13 David G. Johnston <[email protected]>
parent: Ayush Vatsa <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2024-06-18 17:13 UTC (permalink / raw)
To: Ayush Vatsa <[email protected]>; +Cc: [email protected]
On Tue, Jun 18, 2024 at 9:50 AM Ayush Vatsa <[email protected]>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
@ 2024-06-18 17:15 David G. Johnston <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2024-06-18 17:15 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Tue, Jun 18, 2024 at 10:07 AM Ron Johnson <[email protected]>
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.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
@ 2024-06-18 17:54 Ron Johnson <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Ron Johnson @ 2024-06-18 17:54 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
On Tue, Jun 18, 2024 at 1:16 PM David G. Johnston <
[email protected]> wrote:
> On Tue, Jun 18, 2024 at 10:07 AM Ron Johnson <[email protected]>
> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
@ 2024-06-18 17:57 David G. Johnston <[email protected]>
parent: Ron Johnson <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2024-06-18 17:57 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On Tuesday, June 18, 2024, Ron Johnson <[email protected]> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
@ 2024-06-19 16:46 Ayush Vatsa <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Ayush Vatsa @ 2024-06-19 16:46 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
@ 2024-06-19 16:50 David G. Johnston <[email protected]>
parent: Ayush Vatsa <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2024-06-19 16:50 UTC (permalink / raw)
To: Ayush Vatsa <[email protected]>; +Cc: [email protected] <[email protected]>
On Wednesday, June 19, 2024, Ayush Vatsa <[email protected]> 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-06-19 16:50 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-18 16:50 Seeking Clarification on Function Definitions in PostgreSQL Extensions Ayush Vatsa <[email protected]>
2024-06-18 17:07 ` Ron Johnson <[email protected]>
2024-06-18 17:15 ` David G. Johnston <[email protected]>
2024-06-18 17:54 ` Ron Johnson <[email protected]>
2024-06-18 17:57 ` David G. Johnston <[email protected]>
2024-06-18 17:13 ` David G. Johnston <[email protected]>
2024-06-19 16:46 ` Ayush Vatsa <[email protected]>
2024-06-19 16:50 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox