From: Dennis Gearon <gearond@fireserve.net>
To: newsy@lewczuk.com
Cc: Lista dyskusyjna pgsql-general <pgsql-general@postgresql.org>
Cc: Lista dyskusyjna pgsql-sql <pgsql-sql@postgresql.org>
Cc: Lista dyskusyjna pgsql-bugs <pgsql-bugs@postgresql.org>
Subject: Re: [GENERAL] plPGSQL bug in function creation
Date: Mon, 08 Sep 2003 07:15:23 -0700
Message-ID: <3F5C8EFB.7090101@fireserve.net> (raw)
In-Reply-To: <004901c375e3$bd31f660$4701a8c0@marek>
References: <004901c375e3$bd31f660$4701a8c0@marek>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gearond@fireserve.net, newsy@lewczuk.com, pgsql-general@postgresql.org, pgsql-bugs@postgresql.org
Subject: Re: [GENERAL] plPGSQL bug in function creation
In-Reply-To: <3F5C8EFB.7090101@fireserve.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox