Re: [BUGS] [GENERAL] plPGSQL bug in function creation

#1

On Monday 08 September 2003 09:32, Marek Lewczuk wrote:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#2

Marek Lewczuk wrote:

Quote:

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

---------------------------(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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] [GENERAL] plPGSQL bug in function creation

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] [GENERAL] plPGSQL bug in function creation - 09-09-2003 , 08:05 PM

Re: [BUGS] [GENERAL] plPGSQL bug in function creation - 09-09-2003 , 08:11 PM