plpgsql/rule question

"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

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

"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

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/

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