Re: UPDATE an updatable view - Mailing list pgsql-general

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@aklaver.com