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.
posted by Pavel Stěhule @ 11:22 PM
7 Comments
7 Comments:
Should plpgsql parser check this case and produce error, e.g. "ambiguous variable name detected" or whatever?
This is result of plpgsql_check_function for this case
postgres=# select * from plpgsql_check_function('foo');
┌───────────────────────────────────────────────────────────────────────────────┐
│ plpgsql_check_function │
╞═══════════════════════════════════════════════════════════════════════════════╡
│ warning:00000:3:statement block:name of variable "offset" is reserved keyword │
│ Detail: The reserved keyword was used as variable name. │
│ error:42601:4:RETURN:query "SELECT offset + foo(a)" returned 0 columns │
└───────────────────────────────────────────────────────────────────────────────┘
(3 rows)
i love plpgsql check, thanks for yuor work,
i never use key word, but when i find it in some client code i put double quote ""
CREATE OR REPLACE FUNCTION public.fx()
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE "offset" int DEFAULT 0;
BEGIN
RETURN "offset" + 1;
END;
mia=# select fx();
fx
----
1
(1 fila)
$function$
@Anthony
yes, it can works. What I known, it is not officially supported feature - but it is working. Still better to use different name or some prefix.
@pavel
I totally agree with you, and I suggest the same thing you say, but the clients of some migrations from oracle are ...
ok. I tested using labels, and it is working too.
postgres=# create or replace function foo(a int)
returns int as $$
<<main>>
declare offset int = 10;
begin
return 1 + main.offset + a;
end;
$$ language plpgsql;
Good observation
Post a Comment
Subscribe to Post Comments [Atom]
<< Home