Re: 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