2

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=> 
  • 1
    Have you tried the same with quoting the reserved word loop inside plpgsql? In lower case: "loop". – Erwin Brandstetter Sep 16 '14 at 21:52
  • @ErwinBrandstetter I confirm that when loop is quoted in the plpgsql code, it works. – Marcus Junius Brutus Sep 16 '14 at 21:55
  • @ErwinBrandstetter That works. But why? Is loop reserved in pg/SQL? – ypercubeᵀᴹ Sep 16 '14 at 21:55
  • 1
    @ypercube: I don't think that's how it should be, since the column name is table qualified (NEW.loop) and should be unambiguous that way, but obviously the parser is confused if the keyword loop is used as unquoted identifier. That aside, it's generally a bad idea to use key words as identifiers ... – Erwin Brandstetter Sep 16 '14 at 22:00
  • @ErwinBrandstetter Have you got a link with pgSQL keywords? Because LOOP is not in postgresql.org/docs/current/static/sql-keywords-appendix.html – ypercubeᵀᴹ Sep 16 '14 at 22:03
3

As it turns out, the key word loop needs to be double-quoted inside PL/pgSQL or the parser is confused:

SELECT public.foo_fun(NEW."loop") INTO fRV;

The situation in Postgres 9.3 is unchanged:
SQL fiddle with quotes (works).
SQL fiddle without quotes (doesn't work).

It's generally a good idea to avoid typical key words like loop as identifiers. There are some plpgsql key words in addition to SQL key words (like for procedural elements or exception handling), but I don't know of a comprehensive list of additional plpgsql key words. The source code would be my best idea.

I found a related discussion on pgsql-hackers from 2007. It seems the matter is not fully resolved, yet.
Didn't see any changes here for Postgres 9.4 either ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.