Re: Altering array(composite-types) without breaking code when inserting them and similar questions

On Sun, Apr 27, 2014 at 4:57 PM, Dorian Hoxha <dorian(dot)hoxha(at)gmail(dot)com> 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