parameters to pl/pgSQL functions

From: David Gardner <david(at)gardnerit(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: parameters to pl/pgSQL functions
Date: 2007-06-26 17:03:04
Message-ID: 468146C8.8010307@gardnerit.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2007-06-26 17:45:23 Re: parameters to pl/pgSQL functions
Previous Message Phillip Nelson 2007-06-26 16:39:22 Insert Question