From: | Jaime Casanova <systemguards(at)gmail(dot)com> |
---|---|
To: | Assad Jarrahian <jarraa(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: help with writing stored procedure |
Date: | 2005-11-10 19:10:17 |
Message-ID: | c2d9e70e0511101110j1f5d754ft8f7cc97b0291026c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/10/05, Assad Jarrahian <jarraa(at)gmail(dot)com> wrote:
> 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
>
I think you must be using ANY(array) not ALL(array)
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
From | Date | Subject | |
---|---|---|---|
Next Message | Carlos Oliva | 2005-11-10 19:13:23 | Performance of autovacuum and full vacuum of database |
Previous Message | Vivek Khera | 2005-11-10 19:06:11 | Re: How to install Slony in windows |