From: | Assad Jarrahian <jarraa(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | help with writing stored procedure |
Date: | 2005-11-10 18:57:29 |
Message-ID: | 4bd3e1480511101057x3a9347aalffc4bae34da6d3c7@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I am trying to write a stored procedure that takes as input an array
(one or more integers) and returns all rows matching that ID (primary
key of the table):
I have this so far:
CREATE OR REPLACE FUNCTION
getLMs(int[],float(8), float(8)) RETURNS SETOF tp_lm_object AS $$
DECLARE
myrec record;
requestIds ALIAS for $1;
latitude ALIAS for $2;
longitude ALIAS for $3;
BEGIN
FOR myrec IN SELECT
LMID, LMOrigin ,LMType,
FROM lostMass
WHERE LMID = ALL (requestIDs) LOOP RETURN NEXT myrec; END LOOP;
RETURN; END;
$$ LANGUAGE 'plpgsql';
When I type in (psql):
SELECT * FROM getLMs( '{3,4}', 34.0,34.0);
it returns nothing (even though there is a entry inside the table with
ID =3 and one with 4)
additionally when I call the command with just one entry inside the array
SELECT * FROM getLMs( '{3}', 34.0,34.0);
I get the following error:
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "getlms" line 10 at return next
I think I am doing something wrong. Your help is appreciated.
Thanks.
-assad
From | Date | Subject | |
---|---|---|---|
Next Message | Vivek Khera | 2005-11-10 19:06:11 | Re: How to install Slony in windows |
Previous Message | Aly Dharshi | 2005-11-10 18:51:57 | Re: [ANNOUNCE] PostgreSQL 8.1.0 RPMs are available for |