UPDATE an updatable view

From: David Nelson <dnelson77808(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: UPDATE an updatable view
Date: 2015-08-27 16:07:09
Message-ID: CANxyCUFtg+T8rKFs6Jnci4UNirUQvg3A7yZMgm47A4n6-oPmaQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Thanks!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2015-08-27 16:20:56 Re: UPDATE an updatable view
Previous Message Alvaro Herrera 2015-08-27 15:51:47 Re: BDR questions?