SEO

#1 (**permalink**) 04-09-2008, 06:57 AM

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

#2 (**permalink**) 04-09-2008, 06:57 AM

On 11/10/05, Assad Jarrahian <jarraa@gmail.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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

help with writing stored procedure