Обсуждение: 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
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
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