UPDATE an updatable view

Поиск
Список
Период
Сортировка
От David Nelson
Тема UPDATE an updatable view
Дата
Msg-id CANxyCUFtg+T8rKFs6Jnci4UNirUQvg3A7yZMgm47A4n6-oPmaQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: UPDATE an updatable view
Список 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!

В списке pgsql-general по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: BDR questions?
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: UPDATE an updatable view