Re: Is a function to a 1-component record type undeclarable?

Hi

po 22. 11. 2021 v 17:00 odesílatel Chapman Flack <chap(at)anastigmatix(dot)net>
napsal:

> Hi,
>
> This example in the docs declares a function returning an anonymous
> 2-component record:
>
> CREATE FUNCTION dup(in int, out f1 int, out f2 text)
> AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
> LANGUAGE SQL;
>
> The same declaration can be changed to have just one OUT parameter:
>
> CREATE FUNCTION dup(in int, out f text)
> AS $$ SELECT CAST($1 AS text) || ' is text' $$
> LANGUAGE SQL;
>
> But it then behaves as a function returning a text scalar, not a record.
> It is distinguishable in the catalog though; it has prorettype text,
> proallargtypes {int,text}, proargmodes {i,o}, proargnames {"",f}.
>
> The first declaration can have RETURNS RECORD explicitly added (which
> doesn't change its meaning any).
>
> If RETURNS RECORD is added to the second, this error results:
>
> ERROR: function result type must be text because of OUT parameters
>
> Is that a better outcome than saying "ah, the human has said what he means,
> and intends a record type here"? It seems the case could easily be
> distinguished in the catalog by storing record as prorettype.
>
> Perhaps more surprisingly, the RETURNS TABLE syntax for the set-returning
> case has the same quirk; RETURNS TABLE (f text) behaves as setof text
> rather than setof record. Again it's distinguishable in the catalog,
> this time with t in place of o in proargmodes.
>
> In this case, clearly the meaning of RETURNS TABLE with one component
> can't be changed, as it's already established the way it is, but the
> equivalent syntax with one OUT parameter and RETURNS RECORD is currently
> rejected with an error just as in the non-SETOF case, so would it not
> be equally feasible to just allow that syntax and let it mean what it says?
>

I agree so this is not consistent, but changing semantics of RETURNS TABLE
after 12 years is not good (and possible), and implemented behaviour has
some logic based on the possibility to work with scalars in scalar and
tabular contexts in Postgres. In this time lateral join was not implemented
and sometimes the tabular functions had to be used in scalar context.
Moreover, the overhead of work with composite types had much more impact
than now, and there was strong preference to use composite types only when
it was really necessary.

My opinions about allowing RETURNS RECORD for one column table is neutral.
It can be relatively natural syntax for enforcing output composite type. On
second hand, usually in usual usage, there is not a big difference if
functions return a scalar or one column table (after implicit expansion),
and the composite type should be slower. But if you need to make a
composite, you can use just the ROW constructor.

postgres=# select row(g) from generate_series(1, 3) g(v);
┌─────┐
│ row │
╞═════╡
│ (1) │
│ (2) │
│ (3) │
└─────┘
(3 rows)

postgres=# select (row(g)).* from generate_series(1, 3) g(v);
┌────┐
│ f1 │
╞════╡
│ 1 │
│ 2 │
│ 3 │
└────┘
(3 rows)

And if we allow RETURNS RECORD, then there will be new inconsistency
between OUT variables and RETURNS TABLE, so at the end I don't see stronger
benefits than negatives.

Do you have some real use cases, where proposed functionality will carry
some benefit?

I remember, when I started with Postgres, and when I started hacking
Postgres I had a lot of problems with implicit unpacking of composite types
somewhere and with necessity to support scalar and composite values as
separate classes. I agree, so this is a real issue, but the beginnings of
this issue are 20 years ago, maybe 40 years ago, when composite types were
introduced, and when composite types were mapped to SQL tables (when QEL
was replaced by SQL). I don't think so now it is possible to fix it.

> Regards,
> -Chap
>
>
> In passing, I also noticed RETURNS TABLE () is a syntax error. I have
> no use case in mind for a function returning an empty composite result,
> but I note that we do allow zero-column tables and empty composite types.
> And it still has 1 bit of entropy: you can tell an empty composite value
> from a null.
>

If RETURNS TABLE (1col) returns scalar, then RETURNS TABLE () does not make
any sense. The sequence 0 .. empty record, 1 scalar, 2+ composite looks
scary too.

Regards

Pavel