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?
And it has to be an BEFORE trigger?
Regards
Alex
