BUG #15218: compilation of a function is correct while its execution is in error

On 30/05/18 06:03, PG Bug reporting form wrote:

I create the following function with an error in it :
CREATE OR REPLACE FUNCTION public.fnc_count_vowels (p_input text)
RETURNS integer LANGUAGE plpgsql AS
$function$
DECLARE
str text;
ret integer;
i integer;
len integer;
tmp text;
BEGIN
str := upperXXX(p_input);
ret := 0;
i := 1;
len := length(p_input);
WHILE i <= len LOOP
IF substr(str, i, 1) in ('A', 'E', 'I', 'O', 'U') THEN
SELECT pg_sleep(1) INTO tmp;
ret := ret + 1;
END IF;
i := i + 1;
END LOOP;
RETURN ret;
END;
$function$
;
-> I use upperXXX which does not exist. normally the compilation should
crash
When I execute the function :
devops=# select fnc_count_vowels('Hello') ;
ERROR: function upperxxx(text) does not exist
LINE 1: SELECT upperXXX(p_input)
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
QUERY: SELECT upperXXX(p_input)
CONTEXT: PL/pgSQL function fnc_count_vowels(text) line 9 at assignment

-> I get the error.
normally this error should have been detected at compilation. (cf Oracle for
instance).

That is intentional, function bodies are not only checked for basic
syntax at CREATE FUNCTION time.

It gives you flexibility, you can use functions and tables in the
function that are created after the function, or in the function itself.
For example, you can do this:

CREATE OR REPLACE FUNCTION public.fnc_count_vowels (p_input text)
RETURNS integer LANGUAGE plpgsql AS
$function$
DECLARE
str text;
ret integer;
i integer;
len integer;
tmp text;
BEGIN

CREATE OR REPLACE FUNCTION upperXXX(text) RETURNS text AS 'SELECT
upper($1)' LANGUAGE SQL;

str := upperXXX(p_input);

ret := 0;
i := 1;
len := length(p_input);
WHILE i <= len LOOP
IF substr(str, i, 1) in ('A', 'E', 'I', 'O', 'U') THEN
SELECT pg_sleep(1) INTO tmp;
ret := ret + 1;
END IF;
i := i + 1;
END LOOP;
RETURN ret;
END;
$function$;

It's not common to do that with functions, but people do that with
temporary tables all the time.

- Heikki

Hello
I suggest use plpgsql_check extension: https://github.com/okbob/plpgsql_check/ It can found some possible errors in plpgsql code.
Postgresql itself does not check function body correctnes on create.

regards, Sergei