DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=NvIxz1MCYV5YPN7lNx367euW07s+d9KcDyEizUe/VAu+51cinT/nkw3iGP+HtkVhjl574FehdeMBPVLSGimZpvz3usfzGHYCykz8xt5faRIJQYVRt6h9asaJEJg2Ibm7lIiGyjASCA/xKFeE0jqWcTVm1R4LeIxXd9eSD+fMYF8=
Message-ID: <d7ba27305091616046e23e171@mail.gmail.com>
Date: Fri, 16 Sep 2005 19:04:39 -0400
From: Kenneth Dombrowski <kdombrowski@gmail.com>
Reply-To: kdombrowski@gmail.com
To: pgsql-sql@postgresql.org
Subject: plpgsql function not accepting NULL value
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,=20

I'm using the postgresql 7.4.7-6sarge1 package from debian stable.

I am confused about these two functions, and their behavior when being
given NULL values in place of a parameter declared as integer:

This one works as expected:=20

create or replace function insert_rate (integer, integer, integer,
integer, numeric)
returns bigint=20
as ' =20
        declare
                x_admin_id              alias for $1;
                x_developer_id          alias for $2;
                x_client_id             alias for $3;
                x_project_id            alias for $4;
                x_rate                  alias for $5;
                x_meta_id       int;
                -- and the return id=20
                x_rate_id bigint;
        begin
        select into x_meta_id insert_meta (x_admin_id);
        insert into rates_tbl (
                developer_id,=20
                client_id,=20
                project_id,=20
                rate,=20
                meta_id
        ) values (
                x_developer_id,=20
                x_client_id,
                x_project_id,
                x_rate,
                x_meta_id
        );
        select into x_rate_id currval(\'rates_tbl_rate_id_seq\');
        return x_rate_id;
        end;
        ' language plpgsql;

invoicer=3D> select insert_rate(1, 1, 1, NULL, '60.00');
 insert_rate
-------------
           7
(1 row)

invoicer=3D> select * from rates_tbl;
 rate_id |  rate  | developer_id | client_id | project_id | meta_id
---------+--------+--------------+-----------+------------+---------
...other rows snipped...
       7 |  60.00 |            1 |         1 |            |     220
(7 rows)


I can't get this one to work at all:

create or replace function update_rate (integer, integer, integer,
        integer, numeric, integer)
returns void
as ' =20
        declare
                x_admin_id              alias for $1;
                x_developer_id          alias for $2;
                x_client_id             alias for $3;
                x_project_id            alias for $4;
                x_rate                  alias for $5;
                x_rate_id               alias for $6;
                x_meta_id               alias for $7;
        begin
        perform update_lastmod (x_meta_id, x_admin_id);
        update rates_tbl set=20
                developer_id    =3D x_developer_id ,=20
                client_id       =3D x_client_id ,=20
                project_id      =3D x_project_id ,=20
                rate            =3D x_rate =20
        where rate_id =3D x_rate_id ;=20
        return ;
        end;
        ' language plpgsql;


invoicer=3D> select update_rate(1, 1, 10, NULL, numeric '90.00', 6, 216);
ERROR:  function update_rate(integer, integer, integer, "unknown",
numeric, integer, integer) does not exist
HINT:  No function matches the given name and argument types. You may
need to add explicit type casts.

I tried adding an explicit cast to "integer" on the NULL parameter,
but just got a syntax error @ "NULL"

The only notable difference I see between the functions, is that the
problem function returns void; I tried changing the return value to
'integer' and changing the 'return;' line to 'return 1;', but there
was no change.

Can someone please explain to me what is causing the problem?=20

Thanks,
Kenneth