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 <mmonc...@gmail.com> wrote: > On Sun, Apr 27, 2014 at 4:57 PM, Dorian Hoxha <dorian.ho...@gmail.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 >
