Postgres Pro
Downloads
Home   >   mailing lists

Thread: plpgsql/rule question

plpgsql/rule question

From
"Ed L."
Date:
Is it possible to tell if a column in a NEW record in a plpgsql function was
explicitly specified as NULL or simply left out altogether?

For example, if I have the following table:

    create table foo(id serial, msg varchar)

Is it possible to distinguish within plpgsql between these two queries?

    insert into foo (msg) values ('Hello')
    insert into foo (id, msg) values (NULL, 'Hello')

TIA.

Ed

Re: plpgsql/rule question

From
Tom Lane
Date:
"Ed L." <pgsql@bluepolka.net> writes:
> For example, if I have the following table:

>     create table foo(id serial, msg varchar)

> Is it possible to distinguish within plpgsql between these two queries?

>     insert into foo (msg) values ('Hello')
>     insert into foo (id, msg) values (NULL, 'Hello')

Well, yes, because the default value in the former case will not be
NULL.  But in general a trigger function cannot tell whether a field
value that happens to equal the default was explicitly specified or
defaulted.  All it can see is the proposed field value; not how that
value was arrived at.

            regards, tom lane

Re: plpgsql/rule question

From
"Ed L."
Date:
On Tuesday January 11 2005 8:40, Tom Lane wrote:
> >
> > Is it possible to distinguish within plpgsql between these two queries?
> >
> >     insert into foo (msg) values ('Hello')
> >     insert into foo (id, msg) values (NULL, 'Hello')
>
> Well, yes, because the default value in the former case will not be
> NULL.  But in general a trigger function cannot tell whether a field
> value that happens to equal the default was explicitly specified or
> defaulted.  All it can see is the proposed field value; not how that
> value was arrived at.

Bad example, but you saw what I was after.  Is that also true for NULL
values for NEW.id in foo_insert() in the following example?

    create view foo_view as select * from foo_table;
    create table foo_table (id serial, msg varchar);
    create function foo_insert that inserts NEW.* into foo_table
    create rule that calls foo_insert(NEW) on insert to foo_view

The reason I ask is because I'd like to allow any explicitly specified
values for the view insert, including NULL, to be passed through to the
table insert and override the column defaults.

Ed

Re: plpgsql/rule question

From
Tom Lane
Date:
"Ed L." <pgsql@bluepolka.net> writes:
> The reason I ask is because I'd like to allow any explicitly specified
> values for the view insert, including NULL, to be passed through to the
> table insert and override the column defaults.

Possibly you can solve your problem by attaching the defaults to the
view, not to the table at all.  ALTER TABLE view ALTER COLUMN col SET
DEFAULT something has worked for a number of PG releases...

            regards, tom lane

Re: plpgsql/rule question

From
Michael Fuhr
Date:
On Tue, Jan 11, 2005 at 07:13:25PM -0700, Ed L. wrote:
>
> Is it possible to tell if a column in a NEW record in a plpgsql function was
> explicitly specified as NULL or simply left out altogether?

Why would you want to?  What problem are you trying to solve?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: plpgsql/rule question

From
"Ed L."
Date:
On Tuesday January 11 2005 8:58, Tom Lane wrote:
> "Ed L." <pgsql@bluepolka.net> writes:
> > The reason I ask is because I'd like to allow any explicitly specified
> > values for the view insert, including NULL, to be passed through to the
> > table insert and override the column defaults.
>
> Possibly you can solve your problem by attaching the defaults to the
> view, not to the table at all.  ALTER TABLE view ALTER COLUMN col SET
> DEFAULT something has worked for a number of PG releases...

Perfect, thanks.  The more I learn, the less I know.

Ed