Thread: plPGSQL bug in function creation

Hello,
I think that there is a bug in plPGSQL - or maybe I don't know something
about this language. Try to create this function


Ok., this is the function created in plPGSQL:

CREATE FUNCTION "public"."test" (text, text) RETURNS text AS'
BEGIN
  IF $1 THEN
    RETURN $1;
  ELSE
    RETURN $2;
  END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

If you will execute SELECT test('tess', 'erer') -> then "tess" will be
returned. If you will execute: SELECT test(NULL, 'buuu'); -> then it
will return NULL, but it should return "buuu". I tried to figure out why
it is happening so i modifye this function to this:

CREATE FUNCTION "public"."test" (text, text) RETURNS text AS'
BEGIN
  RETURN 'test';
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

And when i execute: SELECT test(NULL, 'buuu'); -> it returns me NULL
value, when it should return "buuu". Well I think that something is
wrong here.

If I will modify this function again to this:

CREATE FUNCTION "public"."test" (varchar, varchar) RETURNS text AS'
BEGIN
  IF $1 THEN
    RETURN $1;
  ELSE
    RETURN $2;
  END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Everything is working OK.. So the problem is in TEXT type definition.

I'm using PG 7.3.1 on Win/Cyg

On Monday 08 September 2003 09:32, Marek Lewczuk wrote:
> Hello,
> I think that there is a bug in plPGSQL - or maybe I don't know something
> about this language. Try to create this function
[snip]
> And when i execute: SELECT test(NULL, 'buuu'); -> it returns me NULL
> value, when it should return "buuu". Well I think that something is
> wrong here.
>
> If I will modify this function again to this:
>
> CREATE FUNCTION "public"."test" (varchar, varchar) RETURNS text AS'
[snip]
> Everything is working OK.. So the problem is in TEXT type definition.
>
> I'm using PG 7.3.1 on Win/Cyg

Can't reproduce here:

CREATE OR REPLACE FUNCTION functest1(varchar, varchar) RETURNS varchar AS '
BEGIN
  IF $1 THEN
    RETURN $1;
  ELSE
    RETURN $2;
  END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

CREATE OR REPLACE FUNCTION functest2(text, text) RETURNS varchar AS '
BEGIN
  IF $1 THEN
    RETURN $1;
  ELSE
    RETURN $2;
  END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

SELECT functest1('A','B'), functest1(null,'B'), functest2('A','B'),
functest2(null,'B');

CREATE FUNCTION
CREATE FUNCTION
 functest1 | functest1 | functest2 | functest2
-----------+-----------+-----------+-----------
 A         | B         | A         | B
(1 row)

richardh=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96

Not sure whether this is because of the change 7.3.1->7.3.2 or cygwin vs
linux. Don't have a copy of 7.3.1 to check against, sorry. Perhaps check the
changes list to see if it mentions something like this.

--
  Richard Huxton
  Archonet Ltd

Marek Lewczuk wrote:

>Hello,
>I think that there is a bug in plPGSQL - or maybe I don't know something
>about this language. Try to create this function
>
>
>Ok., this is the function created in plPGSQL:
>
>CREATE FUNCTION "public"."test" (text, text) RETURNS text AS'
>BEGIN
>  IF $1 THEN
>    RETURN $1;
>  ELSE
>    RETURN $2;
>  END IF;
>END;
>'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
>If you will execute SELECT test('tess', 'erer') -> then "tess" will be
>returned. If you will execute: SELECT test(NULL, 'buuu'); -> then it
>will return NULL, but it should return "buuu". I tried to figure out why
>it is happening so i modifye this function to this:
>
>CREATE FUNCTION "public"."test" (text, text) RETURNS text AS'
>BEGIN
>  RETURN 'test';
>END;
>'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
>And when i execute: SELECT test(NULL, 'buuu'); -> it returns me NULL
>value, when it should return "buuu". Well I think that something is
>wrong here.
>
>If I will modify this function again to this:
>
>CREATE FUNCTION "public"."test" (varchar, varchar) RETURNS text AS'
>BEGIN
>  IF $1 THEN
>    RETURN $1;
>  ELSE
>    RETURN $2;
>  END IF;
>END;
>'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
>Everything is working OK.. So the problem is in TEXT type definition.
>
>I'm using PG 7.3.1 on Win/Cyg
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>
You can only test for NULL with 'IS NULL'.

NULL is NOT:
    FALSE, 0, or F

"Marek Lewczuk" <newsy@lewczuk.com> writes:
> CREATE FUNCTION "public"."test" (text, text) RETURNS text AS'
> BEGIN
>   IF $1 THEN
>     RETURN $1;
>   ELSE
>     RETURN $2;
>   END IF;
> END;
> 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

If there's a bug here at all, it's that this function doesn't report a
type violation.  What in the world do you think the semantics of that
IF-test are?  text is not boolean.

            regards, tom lane

I had the same success using 7.3.2 with Cygwin:

e=# SELECT functest1('A','B'), functest1(null,'B'), functest2('A','B'),
functest2(null,'B');

 functest1 | functest1 | functest2 | functest2
-----------+-----------+-----------+-----------
 A         | B         | A         | B
(1 row)

e=# select version();
                                         version
----------------------------------------------------------------------------
----------
PostgreSQL 7.3.2 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.2 20020927
(prerelease)
(1 row)

George

- snip -

> Not sure whether this is because of the change 7.3.1->7.3.2 or cygwin vs
> linux. Don't have a copy of 7.3.1 to check against, sorry. Perhaps check
the
> changes list to see if it mentions something like this.
>
> --
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>