Re: Ad hoc SETOF type definition?

On Tue, Sep 26, 2023 at 1:15 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote

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

I really like this behavior and exploit it heavily, in order to,
*) use tables as variable inputs to functions
*) record marshalling, populate_record, etc
*) type safe variable declaration in plpgsql functions
*) arrays of table type for temporary storage (jsonb is eating into this
case though)
*) dblink replication tricks to migrate data across the wire (baroque with
fdw, but still useful in ad hoc coding)

Granted, from the classic sql programming perspective, this is all
highly exotic and weird. There is an organic beauty though in deep sql or
plpgsql coding that comes out and a lot of it is from the type system :).

In fact, I find the concept that 'tables are types' (which I think you are
implying should ideally not be the case by default) is so brilliant and
profound that it is really what sets postgresql apart from competitive
offerings. Granted, you can do all of the same things with composite
types, json, etc, but tables often do the job handily and safely with less
programming effort and the type naturally extends with the table refinement
over time.

I find that the opposite case, basically, to create composite types is
increasingly rare in practice, with jsonb handling transient and unsafe
cases, and 'table created types' covering most of the rest. A lot of it
comes down to style I guess.

merlin