parameters to pl/pgSQL functions - Mailing list pgsql-novice

From David Gardner
Subject parameters to pl/pgSQL functions
Date
Msg-id 468146C8.8010307@gardnerit.net
Whole thread Raw
Responses Re: parameters to pl/pgSQL functions
List pgsql-novice
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;

pgsql-novice by date:

Previous
From: Phillip Nelson
Date:
Subject: Insert Question
Next
From: Tom Lane
Date:
Subject: Re: parameters to pl/pgSQL functions