Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions

On Tue, Jun 18, 2024 at 9:50 AM Ayush Vatsa <ayushvatsa1810(at)gmail(dot)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.