don't use SQL keywords as PLpgSQL variable names
Yesterday I had a possibility to see some strange runtime error
CREATE OR REPLACE FUNCTION public.fx() RETURNS integer LANGUAGE plpgsql AS $function$ DECLARE offset int DEFAULT 0; BEGIN RETURN offset + 1; END; $function$ postgres=# SELECT fx(); ERROR: query "SELECT offset + 1" returned 0 columns CONTEXT: PL/pgSQL function fx() line 4 at RETURN
What is problem? On first view, the
RETURN
returns 1 column, so error message is strange.But any PLpgSQL expression is a SQL expression - more it is SQL SELECT statement. So
SELECT OFFSET 1
really returns 0 columns.The basic issue is on bad name of variable - it is same like SQL reserved keyword.
OFFSET
is unhappy word. I wrote new check to plpgsql_check, that raises a warning in this situation.