Re: Ad hoc SETOF type definition?

On 9/26/23 13:15, Tom Lane wrote:
> 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.

There might be some other logic in the body of the FOR loop that is not
practical to embed in the body of the SELECT statement.

> 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.

If you're going to only use that type with the function, then an anonymous
record type is Good Enough.

Just like anonymous DO blocks are useful.

> (Our
> behavior of automatically making composite types for tables seems to
> me to have been a rather unfortunate choice.)

It makes developers/DBAs lives just that much easier.

--
Born in Arizona, moved to Babylonia.