OLD pseudo relation for INSERT in rules and triggers

From: Rafał Pietrak <rafal(at)zorro(dot)isa-geek(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: OLD pseudo relation for INSERT in rules and triggers
Date: 2013-01-01 18:17:32
Message-ID: 50E3283C.9090207@zorro.isa-geek.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2013-01-01 18:24:54 Re: OLD pseudo relation for INSERT in rules and triggers
Previous Message Adrian Klaver 2013-01-01 17:26:04 Re: rights for schema