Pages

▼

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

7 comments:

  1. Pavlo GolubMarch 1, 2019 at 1:43 AM

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

    ReplyDelete
  2. Pavel StěhuleMarch 1, 2019 at 4:54 AM

    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)

    ReplyDelete
  3. AnonymousMarch 1, 2019 at 6:05 AM

    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$

    ReplyDelete
  4. Pavel StěhuleMarch 1, 2019 at 8:14 AM

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

    ReplyDelete
  5. UnknownMarch 1, 2019 at 4:36 PM

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

    ReplyDelete
  6. Pavel StěhuleMarch 1, 2019 at 9:04 PM

    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;

    ReplyDelete
  7. UnknownMarch 2, 2019 at 12:20 PM

    Good observation

    ReplyDelete