parameters to pl/pgSQL functions

Поиск
Список
Период
Сортировка
От David Gardner
Тема parameters to pl/pgSQL functions
Дата
Msg-id 468146C8.8010307@gardnerit.net
обсуждение исходный текст
Ответы Re: parameters to pl/pgSQL functions  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Phillip Nelson
Дата:
Сообщение: Insert Question
Следующее
От: Tom Lane
Дата:
Сообщение: Re: parameters to pl/pgSQL functions