Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

parameters to pl/pgSQL functions



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;



Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group