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

So :

1. drop function
2. alter type: add column
3. create again function with new default argument in a transaction ?

On Tue, Apr 29, 2014 at 4:22 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

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