set client_min_messages to warning; create extension if not exists plpgsql_check; set client_min_messages to notice; -- -- Tests of the parser used for the function names passed to the -- plpgsql_check functions, and of the tokenizer and the parsers of -- the arguments of the pragmas. -- -- -- function name or signature -- create function pr_fx(a int) returns int as $$ begin return a; end; $$ language plpgsql; create function "pr FX"(a int) returns int as $$ begin return a; end; $$ language plpgsql; create function "pr""q"(a int) returns int as $$ begin return a; end; $$ language plpgsql; -- an overloaded function cannot be addressed by a name create function pr_over(a int) returns int as $$ begin return a; end; $$ language plpgsql; create function pr_over(a text) returns text as $$ begin return a; end; $$ language plpgsql; -- the errors are raised by the parser of the name, so they are catched -- and printed as a value create function pr_name(fnname text) returns text as $$ begin perform plpgsql_check_function(fnname); return 'ok'; exception when others then return sqlstate || ' ' || sqlerrm; end; $$ language plpgsql; -- plain name, with and without the leading and trailing spaces select pr_name('pr_fx'); pr_name --------- ok (1 row) select pr_name(' pr_fx '); pr_name --------- ok (1 row) -- quoted name, a doubled quote inside a quoted name select pr_name('"pr FX"'); pr_name --------- ok (1 row) select pr_name('"pr""q"'); pr_name --------- ok (1 row) -- qualified names select pr_name('public.pr_fx'); pr_name --------- ok (1 row) select pr_name('public."pr FX"'); pr_name --------- ok (1 row) select pr_name('"public" . "pr FX"'); pr_name --------- ok (1 row) -- signatures; the parser stops on the left parenthesis and the rest -- is processed by the regprocedure input function select pr_name('pr_fx(int)'); pr_name --------- ok (1 row) select pr_name('public.pr_fx(integer)'); pr_name --------- ok (1 row) select pr_name('"pr FX"(int)'); pr_name --------- ok (1 row) -- the name is not an identifier select pr_name('"pr FX'); pr_name -------------------------------------------------- 22023 string is not a valid identifier: ""pr FX" (1 row) select pr_name('""'); pr_name ---------------------------------------------- 22023 string is not a valid identifier: """" (1 row) select pr_name('.pr_fx'); pr_name -------------------------------------------------- 22023 string is not a valid identifier: ".pr_fx" (1 row) select pr_name('public.'); pr_name --------------------------------------------------- 22023 string is not a valid identifier: "public." (1 row) select pr_name('public..pr_fx'); pr_name --------------------------------------------------------- 22023 string is not a valid identifier: "public..pr_fx" (1 row) select pr_name('1234'); pr_name ------------------------------------------------ 22023 string is not a valid identifier: "1234" (1 row) select pr_name('pr_fx pr_fx'); pr_name ------------------------------------------------------- 22023 string is not a valid identifier: "pr_fx pr_fx" (1 row) -- the name is an identifier, but the function is not there or the -- name is not unique select pr_name('pr_missing'); pr_name -------------------------------------------- 42883 function "pr_missing" does not exist (1 row) select pr_name('pr_over'); pr_name ---------------------------------------------- 42725 more than one function named "pr_over" (1 row) drop function pr_name(text); drop function pr_over(text); drop function pr_over(int); drop function "pr""q"(int); drop function "pr FX"(int); drop function pr_fx(int); -- -- tokenizer -- -- The tokenizer is shared by all the pragmas and by the in-comment -- options. The echo option is used here, because it is the only place -- where a token of any type is accepted and printed back, so the result -- of the tokenization is visible. create function pr_tokens() returns void as $func$ -- @plpgsql_check_options: echo = 'a string with a '' quote' -- @plpgsql_check_options: echo = "a quoted identifier with a "" quote" -- @plpgsql_check_options: echo = AnIdentifier -- @plpgsql_check_options: echo = 3.14 begin end; $func$ language plpgsql; select * from plpgsql_check_function('pr_tokens'); NOTICE: comment option "echo" is 'a string with a ' quote' NOTICE: comment option "echo" is "a quoted identifier with a " quote" NOTICE: comment option "echo" is anidentifier NOTICE: comment option "echo" is 3.14 plpgsql_check_function ------------------------ (0 rows) drop function pr_tokens(); -- -- pragma "settype" -- create type pr_ctype as (a int, b int); create table pr_tab(a int, b int); create function pr_settype() returns void as $$ <> declare r record; n int; begin -- a plain, a qualified and a quoted type name perform plpgsql_check_pragma('type: r pr_ctype'); perform plpgsql_check_pragma('type: lbl.r public.pr_ctype'); perform plpgsql_check_pragma('type: "lbl"."r" public."pr_ctype"'); -- a table can be used as a type too perform plpgsql_check_pragma('type: r pr_tab'); raise notice '%', r.a; raise notice '%', n; end; $$ language plpgsql; select * from plpgsql_check_function('pr_settype'); plpgsql_check_function ------------------------ (0 rows) -- errors of the "settype" pragma create or replace function pr_settype() returns void as $$ declare r record; n int; begin perform plpgsql_check_pragma('type: missing_var pr_ctype'); perform plpgsql_check_pragma('type: n pr_ctype'); perform plpgsql_check_pragma('type: r'); perform plpgsql_check_pragma('type: 1 pr_ctype'); perform plpgsql_check_pragma('type: r pr_ctype x'); raise notice '%', r.a; raise notice '%', n; end; $$ language plpgsql; select * from plpgsql_check_function('pr_settype'); WARNING: Pragma "type" on line 6 is not processed. DETAIL: Cannot to find variable "missing_var" used in settype pragma WARNING: Pragma "type" on line 7 is not processed. DETAIL: Pragma "settype" can be applied only on variable of record type WARNING: Pragma "type" on line 8 is not processed. DETAIL: Syntax error (expected identifier) WARNING: Pragma "type" on line 9 is not processed. DETAIL: Syntax error (expected identifier) WARNING: Pragma "type" on line 10 is not processed. DETAIL: syntax error at or near "x" plpgsql_check_function ---------------------------------------------------------------------------- error:55000:11:RAISE:record "r" is not assigned yet Detail: The tuple structure of a not-yet-assigned record is indeterminate. Context: PL/pgSQL expression "r.a" (3 rows) drop function pr_settype(); -- -- the type parser -- -- The type used by the "settype" and "table" pragmas is parsed by -- plpgsql_check itself, so all the accepted forms are checked here. create function pr_type() returns void as $$ declare r record; begin -- a multiword type name, a type modifier, a list of type modifiers perform plpgsql_check_pragma('type: r (a double precision, b varchar(10), c numeric(10,2))'); -- an array, with and without a dimension perform plpgsql_check_pragma('type: r (a int[], b int[3], c pg_catalog.varchar[])'); -- a composite type copied from a table perform plpgsql_check_pragma('type: r (like pr_tab)'); raise notice '%', r.a; end; $$ language plpgsql; select * from plpgsql_check_function('pr_type'); plpgsql_check_function ------------------------ (0 rows) -- errors of the type parser create or replace function pr_type() returns void as $$ declare r record; begin perform plpgsql_check_pragma('type: r (like int)'); perform plpgsql_check_pragma('type: r (like pr_tab'); perform plpgsql_check_pragma('type: r (1 int)'); perform plpgsql_check_pragma('type: r (a int b int)'); perform plpgsql_check_pragma('type: r (a numeric(x))'); perform plpgsql_check_pragma('type: r (a numeric(10'); perform plpgsql_check_pragma('type: r (a numeric(10 2))'); perform plpgsql_check_pragma('type: r (a int[)'); perform plpgsql_check_pragma('type: r (a int['); perform plpgsql_check_pragma('type: r (a "unclosed)'); raise notice '%', r.a; end; $$ language plpgsql; select * from plpgsql_check_function('pr_type'); WARNING: Pragma "type" on line 4 is not processed. DETAIL: "integer" is not composite type WARNING: Pragma "type" on line 5 is not processed. DETAIL: Syntax error (expected ")") WARNING: Pragma "type" on line 6 is not processed. DETAIL: Syntax error (expected identifier) WARNING: Pragma "type" on line 7 is not processed. DETAIL: syntax error at or near "b" WARNING: Pragma "type" on line 8 is not processed. DETAIL: Syntax error (expected number for typmod specification) WARNING: Pragma "type" on line 9 is not processed. DETAIL: Syntax error (unclosed typmod specification) WARNING: Pragma "type" on line 10 is not processed. DETAIL: Syntax error (expected "," in typmod list) WARNING: Pragma "type" on line 11 is not processed. DETAIL: Syntax error (expected "]") WARNING: Pragma "type" on line 12 is not processed. DETAIL: Syntax error (unclosed array specification) WARNING: Pragma "type" on line 13 is not processed. DETAIL: Syntax error (unclosed quoted identifier) plpgsql_check_function ---------------------------------------------------------------------------- error:55000:14:RAISE:record "r" is not assigned yet Detail: The tuple structure of a not-yet-assigned record is indeterminate. Context: PL/pgSQL expression "r.a" (3 rows) drop function pr_type(); -- -- pragma "table" -- create function pr_table() returns void as $$ declare r record; begin perform plpgsql_check_pragma('table: pr_tt1(a int, b int)'); perform plpgsql_check_pragma('table: pg_temp.pr_tt2(a int)'); perform plpgsql_check_pragma('table: pg_temp."pr""tt3"(a int)'); select * from pr_tt1 into r; select * from pr_tt2 into r; raise notice '%', r.a; end; $$ language plpgsql; select * from plpgsql_check_function('pr_table'); plpgsql_check_function ------------------------ (0 rows) -- errors of the "table" pragma create or replace function pr_table() returns void as $$ begin perform plpgsql_check_pragma('table: public.pr_tt4(a int)'); perform plpgsql_check_pragma('table: 1(a int)'); perform plpgsql_check_pragma('table: pg_temp.1(a int)'); perform plpgsql_check_pragma('table: pr_tt5'); perform plpgsql_check_pragma('table: pr_tt6(a int) x'); perform plpgsql_check_pragma('table: "pr_tt7(a int)'); -- a composite type is not allowed as a type of a column of a table -- created by this pragma perform plpgsql_check_pragma('table: pr_tt8(a (b int))'); end; $$ language plpgsql; select * from plpgsql_check_function('pr_table'); WARNING: Pragma "table" on line 3 is not processed. DETAIL: schema "public" cannot be used in pragma "table" (only "pg_temp" schema is allowed) WARNING: Pragma "table" on line 4 is not processed. DETAIL: Syntax error (expected identifier) WARNING: Pragma "table" on line 5 is not processed. DETAIL: Syntax error (expected identifier) WARNING: Pragma "table" on line 6 is not processed. DETAIL: Syntax error (expected table specification) WARNING: Pragma "table" on line 7 is not processed. DETAIL: Syntax error (unexpected chars after table specification) WARNING: Pragma "table" on line 8 is not processed. DETAIL: Syntax error (unclosed quoted identifier) WARNING: Pragma "table" on line 11 is not processed. DETAIL: Cannot to create table with pseudo-type record. plpgsql_check_function ------------------------ (0 rows) drop function pr_table(); -- -- pragma "sequence" -- create function pr_sequence() returns void as $$ begin perform plpgsql_check_pragma('sequence: pr_sq1'); perform plpgsql_check_pragma('sequence: pg_temp.pr_sq2'); perform plpgsql_check_pragma('sequence: pg_temp."pr""sq3"'); perform nextval('pg_temp.pr_sq1'); perform nextval('pg_temp.pr_sq2'); end; $$ language plpgsql; select * from plpgsql_check_function('pr_sequence'); plpgsql_check_function ------------------------ (0 rows) -- errors of the "sequence" pragma create or replace function pr_sequence() returns void as $$ begin perform plpgsql_check_pragma('sequence: public.pr_sq4'); perform plpgsql_check_pragma('sequence: 1'); perform plpgsql_check_pragma('sequence: pg_temp.1'); perform plpgsql_check_pragma('sequence: pr_sq5 x y'); perform plpgsql_check_pragma('sequence: "pr_sq6'); end; $$ language plpgsql; select * from plpgsql_check_function('pr_sequence'); WARNING: Pragma "sequence" on line 3 is not processed. DETAIL: schema "public" cannot be used in pragma "sequence" (only "pg_temp" schema is allowed) WARNING: Pragma "sequence" on line 4 is not processed. DETAIL: Syntax error (expected identifier) WARNING: Pragma "sequence" on line 5 is not processed. DETAIL: Syntax error (expected identifier) WARNING: Pragma "sequence" on line 6 is not processed. DETAIL: Syntax error (unexpected chars after sequence name) WARNING: Pragma "sequence" on line 7 is not processed. DETAIL: Syntax error (unclosed quoted identifier) plpgsql_check_function ------------------------ (0 rows) drop function pr_sequence(); -- -- pragmas "assert-schema", "assert-table" and "assert-column" -- create function pr_assert() returns void as $$ <> declare v_schema varchar default 'public'; v_table varchar default 'pr_tab'; v_column varchar default 'a'; begin raise notice '%', format('%I.%I.%I', v_schema, v_table, v_column); perform 'pragma:assert-schema: v_schema'; perform 'pragma:assert-table: v_schema, v_table'; perform 'pragma:assert-table: v_table'; perform 'pragma:assert-column: v_schema, v_table, v_column'; perform 'pragma:assert-column: v_table, v_column'; -- a variable can be addressed by a qualified name too perform 'pragma:assert-schema: lbl.v_schema'; end; $$ language plpgsql; select * from plpgsql_check_function('pr_assert'); plpgsql_check_function ------------------------ (0 rows) -- the asserted objects do not exist create or replace function pr_assert() returns void as $$ declare v_schema varchar default 'pr_missing_schema'; v_table varchar default 'pr_missing_table'; v_column varchar default 'pr_missing_column'; begin perform 'pragma:assert-table: v_table'; end; $$ language plpgsql; select * from plpgsql_check_function('pr_assert'); plpgsql_check_function --------------------------------------------------------------- error:42P01:7:PERFORM:table "pr_missing_table" does not exist (1 row) create or replace function pr_assert() returns void as $$ declare v_schema varchar default 'public'; v_table varchar default 'pr_tab'; v_column varchar default 'pr_missing_column'; begin perform 'pragma:assert-column: v_schema, v_table, v_column'; end; $$ language plpgsql; select * from plpgsql_check_function('pr_assert'); plpgsql_check_function ----------------------------------------------------------------------------------------------- error:42703:7:PERFORM:column "pr_missing_column" of relation "public"."pr_tab" does not exist (1 row) -- errors of the "assert" pragmas; they are reported as warnings and the -- check continues create or replace function pr_assert() returns void as $$ <> declare v_schema varchar default 'public'; v_table varchar default 'pr_tab'; v_column varchar default 'a'; v_var varchar; begin -- an unknown variable, a name with too many parts perform 'pragma:assert-schema: v_missing'; perform 'pragma:assert-schema: a.b.c.d'; -- a variable without an assigned constant perform 'pragma:assert-schema: v_var'; -- a missing comma between the variables perform 'pragma:assert-table: v_schema v_table'; -- too many and too few variables; the parser reads three names at -- most, so a fourth one is reported as an unexpected text perform 'pragma:assert-schema: v_schema, v_table'; perform 'pragma:assert-table: v_schema, v_table, v_column'; perform 'pragma:assert-column: v_schema, v_table, v_column, v_column'; perform 'pragma:assert-column: v_table'; -- a syntax error in the name of the variable perform 'pragma:assert-schema: 1'; end; $$ language plpgsql; select * from plpgsql_check_function('pr_assert'); WARNING: "assert-schema" on line 10 is not processed. DETAIL: Cannot to find variable "v_missing" used in "assert-schema" pragma WARNING: "assert-schema" on line 11 is not processed. DETAIL: Cannot to find variable "a"."b"."c"."d" used in "assert-schema" pragma WARNING: "assert-schema" on line 13 is not processed. DETAIL: Variable "v_var" has not assigned constant WARNING: "assert-table" on line 15 is not processed. DETAIL: Syntax error (expected ",") WARNING: "assert-schema" on line 18 is not processed. DETAIL: too much variables for "assert-schema" pragma WARNING: "assert-table" on line 19 is not processed. DETAIL: too much variables for "assert-table" pragma WARNING: "assert-column" on line 20 is not processed. DETAIL: Syntax error (unexpected chars after variable) WARNING: "assert-column" on line 21 is not processed. DETAIL: too few variables for "assert-column" pragma WARNING: "assert-schema" on line 23 is not processed. DETAIL: Syntax error (expected identifier) plpgsql_check_function -------------------------------------------------------------- warning:00000:7:DECLARE:unused variable "v_var" warning extra:00000:4:DECLARE:never read variable "v_schema" warning extra:00000:5:DECLARE:never read variable "v_table" warning extra:00000:6:DECLARE:never read variable "v_column" (4 rows) drop function pr_assert(); drop table pr_tab; drop type pr_ctype;