Thread: parameters to pl/pgSQL functions

I recently ran into a little problem with a pl/pgSQL function, I was
able to get it working by removing the variable names for the parameters
and using $1 & $2 instead. I am curious if I am just doing something
incorrectly. The first function fails on the insert statement. I'm
guessing that it has something to do with the ntid variable and the
column names having the same name, however pgsql never complained about
the select statement, and on instances where a record is found it
executes correctly. Any insight in this would be helpful.

The error:
ERROR: syntax error at or near "$1"
SQL state: 42601
Context: PL/pgSQL function "ntgetntlpid" line 6 at SQL statement

Bad Function:

CREATE OR REPLACE FUNCTION ntgetntlpid(lpfundid integer, ntid integer)
RETURNS integer AS
$BODY$DECLARE
   ntlpid integer := 0;
BEGIN
   SELECT "ntLPID" INTO ntlpid FROM "NotificationLP" WHERE "LPFundID" =
lpfundid AND "ntid" = ntid;
   IF NOT FOUND THEN
      INSERT INTO "NotificationLP" ("LPFundID", "ntid") VALUES
(lpfundid,ntid);
      SELECT "ntLPID" INTO ntlpid FROM "NotificationLP" WHERE "LPFundID"
= lpfundid AND "ntid" = ntid;
   END IF;
   return ntlpid;
END;$BODY$  LANGUAGE 'plpgsql' VOLATILE;

This one works:

CREATE OR REPLACE FUNCTION ntgetntlpid(integer, integer) RETURNS integer AS
$BODY$DECLARE
   ntlpid integer := 0;
BEGIN
   SELECT "ntLPID" INTO ntlpid FROM "NotificationLP" WHERE "LPFundID" =
$1 AND "ntid" = $2;
   IF NOT FOUND THEN
      INSERT INTO "NotificationLP" ("LPFundID", "ntid") VALUES ($1,$2);
      SELECT "ntLPID" INTO ntlpid FROM "NotificationLP" WHERE "LPFundID"
= $1 AND "ntid" = $2;
   END IF
   return ntlpid;
END;$BODY$  LANGUAGE 'plpgsql' VOLATILE;

David Gardner <david@gardnerit.net> writes:
> Bad Function:

> CREATE OR REPLACE FUNCTION ntgetntlpid(lpfundid integer, ntid integer)
> RETURNS integer AS
> $BODY$DECLARE
>    ntlpid integer := 0;
> BEGIN
>    SELECT "ntLPID" INTO ntlpid FROM "NotificationLP" WHERE "LPFundID" =
> lpfundid AND "ntid" = ntid;
>    IF NOT FOUND THEN
>       INSERT INTO "NotificationLP" ("LPFundID", "ntid") VALUES
> (lpfundid,ntid);

I think you're hoping that those double quotes prevent the names from
being matched to the plpgsql variables, but this is not so.  "LPFundID"
won't match lpfundid, but that's because of the case differential not
the quotes. "ntid" does match ntid.  So that select is being interpreted
as
    ... WHERE "LPFundID" = $1 AND $2 = $2
which is certainly not what you want; and the insert is failing outright
because of $2 in the column name list.

Moral: don't use variable names that are the same as table or field
names you need to use in the same function.

If you really need to do this, the correct solution is to qualify the
field names, eg
    AND "NotificationLP".ntid = ntid
plpgsql will never think that a dotted name matches a variable.  I fear
that solution won't work for an INSERT column name list item though.

            regards, tom lane

Thanks,
 I just had a huge "ahah!" moment. Because the table in question is new,
it only has a few entries of test data in it, and there is only one
entry of "LPFundID"=$1, so I didn't notice that it was evaluating $2=$2,
and I just thought there was something goofy about the insert statement
itself.
I have no problems renaming the parameter name to ntid_in.
> I think you're hoping that those double quotes prevent the names from
> being matched to the plpgsql variables, but this is not so.  "LPFundID"
> won't match lpfundid, but that's because of the case differential not
> the quotes. "ntid" does match ntid.  So that select is being interpreted
> as
>     ... WHERE "LPFundID" = $1 AND $2 = $2
> which is certainly not what you want; and the insert is failing outright
> because of $2 in the column name list.
>
> Moral: don't use variable names that are the same as table or field
> names you need to use in the same function.
>
> If you really need to do this, the correct solution is to qualify the
> field names, eg
>     AND "NotificationLP".ntid = ntid
> plpgsql will never think that a dotted name matches a variable.  I fear
> that solution won't work for an INSERT column name list item though.
>
>             regards, tom lane
>