Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] plPGSQL bug in function creation

From: Dennis Gearon <gearond(at)fireserve(dot)net>
To: newsy(at)lewczuk(dot)com
Cc: Lista dyskusyjna pgsql-general <pgsql-general(at)postgresql(dot)org>,Lista dyskusyjna pgsql-sql <pgsql-sql(at)postgresql(dot)org>,Lista dyskusyjna pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: [GENERAL] plPGSQL bug in function creation
Date: 2003-09-08 14:15:23
Message-ID: 3F5C8EFB.7090101@fireserve.net (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-generalpgsql-sql
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


In response to

pgsql-sql by date

Next:From: Tom LaneDate: 2003-09-08 14:19:43
Subject: Re: [SQL] plPGSQL bug in function creation
Previous:From: Rod TaylorDate: 2003-09-08 14:11:24
Subject: Re: MINUS & ROWNUM in PostGres

pgsql-bugs by date

Next:From: Tom LaneDate: 2003-09-08 14:19:43
Subject: Re: [SQL] plPGSQL bug in function creation
Previous:From: George WeaverDate: 2003-09-08 12:41:30
Subject: Re: [SQL] [GENERAL] plPGSQL bug in function creation

pgsql-general by date

Next:From: Tom LaneDate: 2003-09-08 14:19:43
Subject: Re: [SQL] plPGSQL bug in function creation
Previous:From: Achilleus MantziosDate: 2003-09-08 12:55:59
Subject: Re: Conditional row grained replication with DBMirror

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group