Thursday, February 28, 2019

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.