2013/3/14 Alexander Farber <alexander.farber@gmail.com>:
> Thank you -
>
> On Thu, Mar 14, 2013 at 10:40 AM, Ian Lawrence Barwick
> <barwick@gmail.com> wrote:
>>> I also have an INSERT trigger on my table,
>>> can I return a NULL from it or something similar?
>>
>> Yes, if you test for the presence of the word you can return NULL
>> and the row will be discarded. See example below.
>>
>>
>> testdb=# CREATE TABLE foo (word TEXT NOT NULL PRIMARY KEY);
>> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
>> "foo_pkey" for table "foo"
>> CREATE TABLE
>> testdb=#
>> testdb=# INSERT INTO foo VALUES('bar'),('baz');
>> INSERT 0 2
>> testdb=# INSERT INTO foo VALUES('bar');
>> ERROR: duplicate key value violates unique constraint "foo_pkey"
>> DETAIL: Key (word)=(bar) already exists.
>>
>>
>> CREATE OR REPLACE FUNCTION foo_check()
>> RETURNS TRIGGER
>> LANGUAGE 'plpgsql'
>> AS
>> $$
>> BEGIN
>> PERFORM TRUE
>> FROM foo
>> WHERE word = NEW.word;
>> IF FOUND THEN
>> RETURN NULL;
>> END IF;
>> RETURN NEW;
>> END;
>> $$;
>>
>> CREATE TRIGGER tr_foo_check
>> BEFORE INSERT ON foo
>> FOR EACH ROW EXECUTE PROCEDURE foo_check();
>>
>> testdb=# INSERT INTO foo VALUES('bar');
>> INSERT 0 0
>
> so the return value of an insert trigger
> is actually what get's inserted?
Yup, normally that would be the contents of the NEW record.
> And it has to be an BEFORE trigger?
Yes, because an AFTER trigger is fired after the row is updated, so
the row can't be changed.
Regards
Ian Barwick