public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tim Landscheidt <[email protected]>
To: [email protected]
Subject: OLD and NEW in PL/pgSQL triggers
Date: Sun, 14 Mar 2010 18:19:20 +0000
Message-ID: <[email protected]> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: OLD and NEW in PL/pgSQL triggers
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox