On Sun, Apr 27, 2014 at 4:57 PM, Dorian Hoxha <dorian.hoxha@xxxxxxxxx> wrote: > Since my alternative is using json, that is heavier (need to store keys in > every row) than composite-types. > Updating an element on a specific composite_type inside an array of them is > done by UPDATE table SET composite[2].x = 24; > > So last standing question, is it possible to insert an array of > composite_types by not specifying all of the columns for each composite_type > ? > So if i later add other columns to the composite_type, the insert query > doesn't break ? One way to do it is via 'type constructor function'. postgres=# create type foo_t as (a int, b int); postgres=# create function foo_t(a int, b int) returns foo_t as $$ select row(a,b)::foo_t; $$ language sql stable; postgres=# create table bar(f foo_t); postgres=# insert into bar VALUES ((1,2)::foo_t); -- not tolerant to changes postgres=# insert into bar VALUES (foo_t(1,2)); -- works fine now, to extend the field, we can overload the function making sure to default the 3rd argument. alter type foo_t add attribute c text; drop function foo_t(int, int); -- must do this to make function unambiguous create function foo_t(a int, b int, c text = null) returns foo_t as $$ select row(a,b,c)::foo_t; $$ language sql stable; postgres=# INSERT INTO bar VALUES ((1,2)::foo_t); postgres=# insert into bar VALUES (foo_t(1,2,'test')); -- works fine This technique is somewhat dubious, but if for whatever reason you absolutely must preserve client sql in the face of server changes it might work. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
