Dynamic Array into pl/pgSQL function

From: "Derrick Betts" <derrick(at)grifflink(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Dynamic Array into pl/pgSQL function
Date: 2004-05-31 05:31:17
Message-ID: 000a01c446d0$7fba6380$0200a8c0@main
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I looked around for an example of how I might accomplish this, but couldn't find anything. Perhaps I'm using the wrong search words.

I want to input dynamic values into a function, with one of those values being a list of numbers:

CREATE OR REPLACE FUNCTION public.PopContacts(varchar, varchar)
RETURNS SETOF casedata AS
'
DECLARE
c casedata%rowtype;
State alias for $1;
ListOfNumbers alias for $2;
rec RECORD;

BEGIN
FOR rec IN SELECT caseid, name, address FROM Table1 WHERE area = State and caseId In (ListOfNumbers)
LOOP
c.caseid := rec.caseid; c.name := rec.name; c.address := rec.name;
RETURN NEXT c;
END LOOP;
RETURN;
END;
'
LANGUAGE 'plpgsql' VOLATILE;

How can I get the ListOfNumbers into the function and then have the function use that ListOfNumbers in the manner shown above? I realize that varchar is not the correct input type for the ListOfNumbers, but am unsure what to use to have it work properly. The length of the ListOfNumbers varies with each call to the function. I am sending a Query string to the server from a client application.

I appreciate any ideas anyone may have.

Thank you,
Derrick

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message derrick 2004-05-31 06:24:39 Re: Dynamic Array into pl/pgSQL function
Previous Message Jeff Eckermann 2004-05-30 17:19:23 Re: PostgreSQL delete the blank in the end of the String automatically. how can I avoid it?