Re: PostgreSQL: Question about rules

On 11/16/06, Jeremy Smith <postgres(at)duckwizard(dot)com> wrote:
> On 11/16/06, Jeff Davis <pgsql(at)j-davis(dot)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