Re: Using psql to feed a file line by line to a table column

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