Re: parameters to pl/pgSQL functions

David Gardner <david(at)gardnerit(dot)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