On 08/27/2015 09:07 AM, David Nelson wrote:
Good morning all,
I am creating an updatable view on a set of tables, and just ran into
unexpected (or more likely misunderstood) behavior with the UPDATE
statement. If an attribute is not explicitly listed in the UPDATE statement,
the NEW value appears to be populated with the OLD value. Unless I'm missing
something, this means there is no way to check to see if the UPDATE
statement
includes references to attributes unless it changes them.
I doubt that statement is is very clear, so let me clarify using Example
39-3 from the 9.1 manual:
\pset expanded on
Expanded display is on.
SELECT VERSION();
-[ RECORD 1
]-----------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.1.18 on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit
DROP TRIGGER emp_stamp ON emp;
ERROR: relation "emp" does not exist
DROP FUNCTION emp_stamp();
ERROR: function emp_stamp() does not exist
DROP TABLE emp;
ERROR: table "emp" does not exist
CREATE TABLE emp
(
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE TABLE
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- Who works for us when she must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
CREATE TRIGGER
INSERT INTO emp
(
empname,
salary,
last_date,
last_user
)
VALUES
(
'John Doe',
45000,
'2015-08-27 09:50:21-05',
'no_such_person'
);
INSERT 0 1
SELECT *
FROM emp;
-[ RECORD 1 ]------------------------
empname | John Doe
salary | 45000
last_date | 2015-08-27 10:15:57.64472
last_user | postgres
UPDATE emp
SET last_user = 'someone_else';
UPDATE 1
SELECT *
FROM emp;
-[ RECORD 1 ]-------------------------
empname | John Doe
salary | 45000
last_date | 2015-08-27 10:16:40.101402
last_user | postgres
So in the UPDATE statement, I only provided a value for last_user. But the
first test of the trigger function tests for a NULL value of
NEW.empname. Since
I did not provide one, I was expecting it to be NULL and an exception to
be thrown. Am I just misunderstanding how things work? Is there any way to
test to see if the UPDATE statement contained a reference to empname? If the
answer is no, I can certainly work with that, but before I go on I wanted
to make sure I wasn't missing anything.
An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is
deleted and the NEW one inserted with the OLD values unless they where
explicitly changed. So in your test NEW.empname is still 'John Doe' and
therefore NOT NULL. That test would only work if someone explicitly set
empname = NULL in the update. If you want to check whether the value has
not been changed then:
IF NEW.empname = OLD.empname THEN
Thanks!
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
