Re: Ad hoc SETOF type definition? - Mailing list pgsql-general

From Merlin Moncure
Subject Re: Ad hoc SETOF type definition?
Date
Msg-id CAHyXU0xJe4batJy2ypPZZip0-5MVWtqFtyF6ezQRr2mWRsAjGQ@mail.gmail.com
Whole thread Raw
In response to Re: Ad hoc SETOF type definition?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
On Tue, Sep 26, 2023 at 1:15 PM Tom Lane <tgl@sss.pgh.pa.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


 

pgsql-general by date:

Previous
From: "Dirschel, Steve"
Date:
Subject: Need help tuning a query
Next
From: Raivo Rebane
Date:
Subject: Re: Right version of jdbc