PL/pgSQL parameter passing bug?

From: Ed Loehr <ed(at)accuros(dot)com>
To: pggeneral <pgsql-general(at)postgreSQL(dot)org>
Subject: PL/pgSQL parameter passing bug?
Date: 2001-03-30 22:43:40
Message-ID: 3AC50C1C.5832F523@accuros.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm seeing actual parameters impacted by the value of another actual
parameter in an unexpected manner. Example test case and example run
included below. This is occuring on 7.0.3 on Redhat. Can anyone confirm
or educate me?

Regards,
Ed Loehr

% uname -a
Linux bingo 2.2.12-20smp #1 SMP Mon Sep 27 10:34:45 EDT 1999 i686 unknown

The problem is that the actual parameters p2 and p3 seem to be impacted
by the value of p1.

Regards,
Ed Loehr

-- Test Case:

DROP FUNCTION foo(INTEGER,INTEGER,INTEGER);
CREATE FUNCTION foo(INTEGER,INTEGER,INTEGER) RETURNS VARCHAR AS
'DECLARE
p1 ALIAS FOR $1;
p2 ALIAS FOR $2;
p3 ALIAS FOR $3;
BEGIN
RAISE NOTICE ''foo(%, %, %)'', p1, p2, p3; -- show them all
IF p2 ISNULL THEN
RAISE EXCEPTION ''foo() received a null value for p2'';
END IF;
RETURN ''ok'';
END;'
LANGUAGE 'plpgsql';

SELECT foo(1,2,3); -- No problems here.
SELECT foo(null,2,3); -- PROBLEM: p2 is not null, but foo() thinks it
is.

Here's an example run on 7.0.3:

emsdb=# CREATE FUNCTION foo(INTEGER,INTEGER,INTEGER) RETURNS VARCHAR AS
emsdb-# 'DECLARE
emsdb'# p1 ALIAS FOR $1;
emsdb'# p2 ALIAS FOR $2;
emsdb'# p3 ALIAS FOR $3;
emsdb'# BEGIN
emsdb'# RAISE NOTICE ''foo(%, %, %)'', p1, p2, p3; -- show them all
emsdb'# IF p2 ISNULL THEN
emsdb'# RAISE EXCEPTION ''foo() received a null value for p2'';
emsdb'# END IF;
emsdb'# RETURN ''ok'';
emsdb'# END;'
emsdb-# LANGUAGE 'plpgsql';
CREATE FUNCTION foo(INTEGER,INTEGER,INTEGER) RETURNS VARCHAR AS
'DECLARE
p1 ALIAS FOR $1;
p2 ALIAS FOR $2;
p3 ALIAS FOR $3;
BEGIN
RAISE NOTICE ''foo(%, %, %)'', p1, p2, p3; -- show them all
IF p2 ISNULL THEN
RAISE EXCEPTION ''foo() received a null value for p2'';
END IF;
RETURN ''ok'';
END;'
LANGUAGE 'plpgsql';
CREATE
emsdb=#
emsdb=# SELECT foo(1,2,3); -- No problems here.
SELECT foo(1,2,3);
NOTICE: foo(1, 2, 3)
foo
-----
ok
(1 row)

emsdb=# SELECT foo(null,2,3); -- PROBLEM: p2 is not null, but foo()
thinks it is.
SELECT foo(null,2,3);
NOTICE: foo(<NULL>, <NULL>, <NULL>)
ERROR: foo() received a null value for p2
emsdb=#

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jim Mercer 2001-03-30 22:50:32 pg_hba.conf and crypt/password
Previous Message Soma Interesting 2001-03-30 22:34:21 RE: dynamic field names in a function.