Re: PostgreSQL: Question about rules

On 11/16/06, Jeremy Smith <postgres@duckwizard.com> wrote:
>  On 11/16/06, Jeff Davis <pgsql@j-davis.com> wrote:
>
>
> >  create rule "child_with_parent_explicit_insert" as
> >         on insert to child_with_parent_explicit do instead (
> >                 insert into parent(id, foo) values(COALESCE
> > ( new.id ,NEXTVAL('parent_id_seq')), new.foo);
> >                 insert into child(id, bar) values(COALESCE
> > (new.id,CURRVAL('parent_id_seq')),  new.bar);
> >         );
> >
> >
> > I'm not sure if this is what you're looking for, but does this help?
> >
> > Regards,
> >         Jeff Davis
> >
> >
>
>

One more thing that would sweeten the deal even further! Not so much
for sequences, but for other columns with default values:

insert into foo(bar) values(COALESCE(new.bar, DEFAULT))

This doesn't work, because DEFAULT is a language construct that is
only defined within the immediate scope of the values(...) list.  Is
there any way I can use COALESCE to defer to the table for the default
value, rather than explicitly specifying it?

I could probably fake this by writing a function to look up the
default value in pg_attrdef and evaluate it - just want to see if
there is a built-in function for this (I can't find one).  Because it
would be a lot of work :-)

Thanks,
Jeremy