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