help with writing stored procedure

Поиск
Список
Период
Сортировка
От Assad Jarrahian
Тема help with writing stored procedure
Дата
Msg-id 4bd3e1480511101057x3a9347aalffc4bae34da6d3c7@mail.gmail.com
обсуждение исходный текст
Ответы Re: help with writing stored procedure  (Jaime Casanova <systemguards@gmail.com>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Magnus Hagander"
Дата:
Сообщение: Re: How to install Slony in windows
Следующее
От: Vivek Khera
Дата:
Сообщение: Re: How to install Slony in windows