OLD pseudo relation for INSERT in rules and triggers
Hello the list,
As far as I can tell from a quick search through postgresql
documentation, the OLD.* pseudorelation is not available for INSERT
triggers and rules. And a little googleing I did, haven't pointed me to
anything relevant to the following. My apology if I miss in my search
such discussion happening earlier (I apreciate a pointer, where I can
see pros and cons that've been raisen back then);
If not, I think having OLD.* in INSERT rules/triggers is worth pondering.
The thing is, that it would be a valuable tool to mimic table-propper
functionality by a view. The OLD.* preudorelation on INSERT could
provide column defaults from the underlaying table definition.
like:
CREATE TABLE test (tm timestamp default now(), info text);
CREATE TABLE test_view AS SELECT * FROM test;
CREATE RULE with_defaults AS ON INSERT to test_view DO INSTEAD INSERT
INTO test (tm,info) VALUES ( //COALESCE(NEW.tm, OLD.tm), NEW.text);
so:
INSERT INTO test_view (info) VALUES ('hello');
and:
INSERT INTO test_view (tm, info) VALUES (null, 'hello');
both work just as if test_view was a TABLE with a default tm value defined.
-R
On 01/01/2013 10:17 AM, Rafał Pietrak wrote:
Hello the list,
As far as I can tell from a quick search through postgresql
documentation, the OLD.* pseudorelation is not available for INSERT
triggers and rules. And a little googleing I did, haven't pointed me to
anything relevant to the following. My apology if I miss in my search
such discussion happening earlier (I apreciate a pointer, where I can
see pros and cons that've been raisen back then);If not, I think having OLD.* in INSERT rules/triggers is worth pondering.
The thing is, that it would be a valuable tool to mimic table-propper
functionality by a view. The OLD.* preudorelation on INSERT could
provide column defaults from the underlaying table definition.like:
CREATE TABLE test (tm timestamp default now(), info text);
CREATE TABLE test_view AS SELECT * FROM test;
CREATE RULE with_defaults AS ON INSERT to test_view DO INSTEAD INSERT
INTO test (tm,info) VALUES ( //COALESCE(NEW.tm, OLD.tm), NEW.text);so:
INSERT INTO test_view (info) VALUES ('hello');
and:
INSERT INTO test_view (tm, info) VALUES (null, 'hello');both work just as if test_view was a TABLE with a default tm value defined.
http://www.postgresql.org/docs/9.2/interactive/sql-alterview.html
"SET/DROP DEFAULT
These forms set or remove the default value for a column. A default
value associated with a view column is inserted into INSERT statements
on the view before the view's ON INSERT rule is applied, if the INSERT
does not specify a value for the column."
It is not quite the same, but close.
-R
--
Adrian Klaver
adrian.klaver@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
W dniu 01/01/2013 07:24 PM, Adrian Klaver pisze:
On 01/01/2013 10:17 AM, Rafał Pietrak wrote:
[---------------]
If not, I think having OLD.* in INSERT rules/triggers is worth
pondering.The thing is, that it would be a valuable tool to mimic table-propper
functionality by a view. The OLD.* preudorelation on INSERT could
provide column defaults from the underlaying table definition.
[----------------]
http://www.postgresql.org/docs/9.2/interactive/sql-alterview.html
"SET/DROP DEFAULT
These forms set or remove the default value for a column. A default
value associated with a view column is inserted into INSERT statements
on the view before the view's ON INSERT rule is applied, if the INSERT
does not specify a value for the column."
Aha. I felt, I was missing something.
It is not quite the same, but close.
It's not the same, as one has to keep track of the default value for one
thing in two places; but it's surely better then nothing (as I thought,
was the case).
thenx again,
-R
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general