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

The following bug has been logged on the website:

Bug reference:      15218
Logged by:          Didier ROS
Email address:      didier.ros@edf.fr
PostgreSQL version: 10.4
Operating system:   CentOS Linux release 7.3.1611 (Core)
Description:

Hi
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).
Thanks in advance
Best Regards
Didier ROS

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
plpgsqlcode.
 
Postgresql itself does not check function body correctnes on create.

regards, Sergei