Re: Ad hoc SETOF type definition?

Ron <ronljohnsonjr(at)gmail(dot)com> writes:
> On 9/26/23 12:46, Tom Lane wrote:
>> Ron<ronljohnsonjr(at)gmail(dot)com> writes:
>>> Is there a way to define the SETOF record on the fly, like you do with
>>> RETURNS TABLE (f1 type1, f2 type2)?

>> Doesn't RETURNS TABLE meet the need already?

> That rationale means that RETURN SETOF is not needed, and can be removed
> from Pg, since "RETURNS TABLE meet the need already".

Indeed, we might not have invented SETOF if RETURNS TABLE were there
first ... but it wasn't. SETOF is from PostQUEL originally I think.
RETURNS TABLE is from some johnny-come-lately addition to the SQL spec.
We're not going to remove SETOF at this point.

> So... can ad hoc SETOF definitions be created in the function definition, or
> is CREATE TYPE the only way to do it?

I'm not really sure what functionality you think is missing from RETURNS
TABLE, granting that you do want to return a set of rows and not exactly
one row. Admittedly, what you get is an anonymous record type and not
a named composite type, but if you want to name the type then I think
having to issue an explicit CREATE TYPE is a good thing. That makes
it clear that the type exists independently of the function. (Our
behavior of automatically making composite types for tables seems to
me to have been a rather unfortunate choice.)

regards, tom lane