CODEWITHSUNDEEP
sqlpostgresqlcasting
begemoth
begemoth

Reputation: 341

PostgreSQL cast record to composite type

As we know, there is a simple way to cast any record to the corresponding composite type in PostgreSQL, like this:

CREATE TYPE test.t_test AS (
   mytext text,
   myint integer
);

SELECT ('text', 10)::test.t_test;  -- will succeed

But one inconvenience with this is that – if the target type is modified (e.g. a field is added) – the cast will then break :(

ALTER TYPE test.t_test ADD ATTRIBUTE mychar char(1);

SELECT ('text', 10)::test.t_test;  -- will fail

CREATE CAST for this case may help but I cannot pass a pseudo-type like RECORD as an argument to a casting function. And neither type-inheritance nor composite type defaults (like table) work either. Is there any other way to achieve compatibility here?

Of course it's possible to use explicit cast-functions like CREATE FUNCTION test.to_t_test(t text, i integer, c char DEFAULT '') RETURNS test.t_test and then do

SELECT test.to_t_test('text', 10)  -- OK
SELECT test.to_t_test('text', 10, '1')  -- OK

then use default parameter values. But this way is neither clear nor comfortable.

Upvotes: 18

Views: 9283

Answers (1)

Chris Travers
Chris Travers

Reputation: 26474

My recommendation is that if you need to do this then take one of the following approaches:

  1. Dynamic discovery of structure (using the pg_attribute catalog table). This is not guaranteed to be future safe but probably is. It also has a bunch of gotchas (don't use attributes with an attnum of less than 0 for example). This is usually the approach I take and have written libraries in Perl for doing such discovery on the client-end.

  2. Create a use type and a storage type and have a cast between them. Thus you can do a SELECT ('text', 10)::test.used_test::test.stored_test and that will work fine. But there is a reason you cannot cast record to a composite type.

Upvotes: 4

Related Questions