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.

7 Comments:

At March 1, 2019 at 1:43 AM , Blogger Pavlo Golub said...

Should plpgsql parser check this case and produce error, e.g. "ambiguous variable name detected" or whatever?

 
At March 1, 2019 at 4:54 AM , Blogger Pavel Stěhule said...

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)

 
At March 1, 2019 at 6:05 AM , Blogger Anthony Rafael Sotolongo León said...

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$

 
At March 1, 2019 at 8:14 AM , Blogger Pavel Stěhule said...

@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.

 
At March 1, 2019 at 4:36 PM , Blogger Anthony Sotolongo said...

@pavel
I totally agree with you, and I suggest the same thing you say, but the clients of some migrations from oracle are ...

 
At March 1, 2019 at 9:04 PM , Blogger Pavel Stěhule said...

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;

 
At March 2, 2019 at 12:20 PM , Blogger Anthony Sotolongo said...

Good observation

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home