pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplPGSQL bug in function creation
5+ messages / 5 participants
[nested] [flat]
* plPGSQL bug in function creation
@ 2003-09-08 08:32 Marek Lewczuk <newsy@lewczuk.com>
0 siblings, 3 replies; 5+ messages in thread
From: Marek Lewczuk @ 2003-09-08 08:32 UTC (permalink / raw)
To: Lista dyskusyjna pgsql-general <pgsql-general@postgresql.org>; pgsql-sql; Lista dyskusyjna pgsql-bugs <pgsql-bugs@postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] plPGSQL bug in function creation
@ 2003-09-08 12:09 Richard Huxton <dev@archonet.com>
parent: Marek Lewczuk <newsy@lewczuk.com>
2 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2003-09-08 12:09 UTC (permalink / raw)
To: newsy@lewczuk.com; Lista dyskusyjna pgsql-general <pgsql-general@postgresql.org>; pgsql-sql; Lista dyskusyjna pgsql-bugs <pgsql-bugs@postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] [GENERAL] plPGSQL bug in function creation
@ 2003-09-08 12:41 George Weaver <georgew1@mts.net>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 5+ messages in thread
From: George Weaver @ 2003-09-08 12:41 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; newsy@lewczuk.com; Lista dyskusyjna pgsql-general <pgsql-general@postgresql.org>; pgsql-sql; Lista dyskusyjna pgsql-bugs <pgsql-bugs@postgresql.org>
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] plPGSQL bug in function creation
@ 2003-09-08 14:15 Dennis Gearon <gearond@fireserve.net>
parent: Marek Lewczuk <newsy@lewczuk.com>
2 siblings, 0 replies; 5+ messages in thread
From: Dennis Gearon @ 2003-09-08 14:15 UTC (permalink / raw)
To: newsy@lewczuk.com; +Cc: Lista dyskusyjna pgsql-general <pgsql-general@postgresql.org>; pgsql-sql; Lista dyskusyjna pgsql-bugs <pgsql-bugs@postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [SQL] plPGSQL bug in function creation
@ 2003-09-08 14:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marek Lewczuk <newsy@lewczuk.com>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2003-09-08 14:19 UTC (permalink / raw)
To: newsy@lewczuk.com; +Cc: Lista dyskusyjna pgsql-general <pgsql-general@postgresql.org>; pgsql-sql; Lista dyskusyjna pgsql-bugs <pgsql-bugs@postgresql.org>
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-09-08 14:19 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-08 08:32 plPGSQL bug in function creation Marek Lewczuk <newsy@lewczuk.com>
2003-09-08 12:09 ` Richard Huxton <dev@archonet.com>
2003-09-08 12:41 ` George Weaver <georgew1@mts.net>
2003-09-08 14:15 ` Dennis Gearon <gearond@fireserve.net>
2003-09-08 14:19 ` Tom Lane <tgl@sss.pgh.pa.us>
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