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;