trigger can't access column loop

2 votes

1 answer

1030 views

postgresql trigger postgresql-9.1 plpgsql reserved-word

                          SSCCE:
The following script:

    $ cat test.sql 
    CREATE TABLE public.foo (
    loop INTEGER
    );
    
    CREATE OR REPLACE FUNCTION public.foo_fun(loop INTEGER) RETURNS BOOLEAN AS $$
        SELECT TRUE;
    $$ LANGUAGE SQL;
    
    CREATE OR REPLACE FUNCTION public.foo_tr_fun() RETURNS TRIGGER AS $$
    DECLARE
        fRV BOOLEAN;
    BEGIN
        SELECT public.foo_fun(NEW.loop) INTO fRV;
    IF fRV THEN
        RETURN NEW;
    ELSE
        RAISE EXCEPTION 'bar';
    END IF;
    END
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER foo_tr AFTER INSERT OR UPDATE ON public.foo
    FOR EACH ROW
    EXECUTE PROCEDURE public.foo_tr_fun();

... which I run with:

    psql -v ON_ERROR_STOP=1 --quiet -X -U some-user -d some-db -f test.sql

... when I then try to insert a row into foo (from psql):

    $ psql -U some-user some-db
    psql (9.1.14)
    Type "help" for help.
    
    RegTAP=> SELECT * FROM public.foo;
     loop 
    ------
    (0 rows)
    
    RegTAP=> INSERT INTO public.foo(loop) VALUES(0);
    ERROR:  record "new" has no field "loop"
    LINE 1: SELECT public.foo_fun(NEW.loop)
                                  ^
    QUERY:  SELECT public.foo_fun(NEW.loop)
    CONTEXT:  PL/pgSQL function "foo_tr_fun" line 5 at SQL statement
    RegTAP=> 

                        

Asked by Marcus Junius Brutus (3409 rep)

Sep 16, 2014, 09:38 PM
Last activity: Sep 16, 2014, 10:30 PM

trigger can't access column loop

Related Questions