From: | Tim Landscheidt <tim(at)tim-landscheidt(dot)de> |
---|---|
To: | pgsql-docs(at)postgresql(dot)org |
Subject: | OLD and NEW in PL/pgSQL triggers |
Date: | 2010-03-14 18:19:20 |
Message-ID: | m3aauaojfb.fsf@passepartout.tim-landscheidt.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
Hi,
<URI:http://www.postgresql.org/docs/8.4/static/plpgsql-trigger.html>
says:
| [...]
| When a PL/pgSQL function is called as a trigger, several
| special variables are created automatically in the top-level
| block. They are:
| NEW
| Data type RECORD; variable holding the new database row
| for INSERT/UPDATE operations in row-level triggers. This
| variable is NULL in statement-level triggers and for DE-
| LETE operations.
| OLD
| Data type RECORD; variable holding the old database row
| for UPDATE/DELETE operations in row-level triggers. This
| variable is NULL in statement-level triggers and for IN-
| SERT operations.
| [...]
Yet, if I try to use that promise:
| tim=# CREATE FUNCTION UniversalTrigger () RETURNS TRIGGER
| tim-# AS $$BEGIN IF OLD IS NULL THEN RAISE NOTICE 'OLD IS NULL'; END IF; END;$$
| tim-# LANGUAGE PLPGSQL;
| CREATE FUNCTION
| tim=# CREATE TABLE TriggerTest (ID SERIAL PRIMARY KEY);
| NOTICE: CREATE TABLE will create implicit sequence "triggertest_id_seq" for serial column "triggertest.id"
| NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "triggertest_pkey" for table "triggertest"
| CREATE TABLE
| tim=# CREATE TRIGGER UniversalTrigger BEFORE DELETE OR INSERT OR UPDATE ON TriggerTest FOR EACH ROW EXECUTE PROCEDURE UniversalTrigger ();
| CREATE TRIGGER
| tim=# INSERT INTO TriggerTest (ID) VALUES (1);
| ERROR: record "old" is not assigned yet
| DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
| CONTEXT: PL/pgSQL function "universaltrigger" line 1 at IF
| tim=#
In my reading,
<URI:http://www.postgresql.org/docs/8.4/static/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS>
states that the syntax is correct. Is this a glitch in the
documentation, a bug or something else?
Tim
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2010-03-17 00:14:01 | Missing information on Expressional Indexes |
Previous Message | Magnus Hagander | 2010-03-08 12:39:08 | Re: [PATCH] Small error in psql's man page |