NEW variable values in trigger functions

From: "Ken Winter" <ken(at)sunward(dot)org>
To: "PostgreSQL pg-general List" <pgsql-general(at)postgresql(dot)org>
Subject: NEW variable values in trigger functions
Date: 2006-01-05 19:13:37
Message-ID: 006801c6122c$21e47880$6603a8c0@kenxp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

1. What is the value of the NEW variable for a column that is not mentioned
in an UPDATE statement? Is it NULL? If not NULL, what?

For example, given this table:

my_tbl (id integer, att1 varchar, att2 varchar)

and a row-wise ON UPDATE OR INSERT trigger function containing this
conditional:

IF NEW.att2 IS NULL THEN
<do stuff>
END IF;

and this UPDATE query:

UPDATE my_tbl SET att1 = 'foo' where id = 1;

will that conditional be satisfied?

2. Same questions re the value of a NEW variable that is not assigned a
value in an INSERT statement.

For example, how would the previous conditional behave in response to:

INSERT INTO my_tbl (id) VALUES (1);

?

3. If an UPDATE query set a column to DEFAULT, what value does a trigger
function see for the column's NEW variable? Is it the string 'DEFAULT', a
reserved word DEFAULT, an empty string, or what?

For example, what would you put in place of <??> in this UPDATE trigger
function:

IF NEW.att2 <??> THEN
<do stuff>
END IF;

to get it to <do stuff> in response to this UPDATE query:

UPDATE my_tbl SET att2 = DEFAULT where id = 1;

?

~ TIA
~ Ken

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2006-01-05 19:21:12 Re: Drop database/create database broken in 7.4?
Previous Message John Meyer 2006-01-05 19:12:08 ACID compliance