Okay. I can send the numbers to the function using this array
format: '{123,124,125,126}'
And the function receives those numbers in this format: CREATE OR REPLACE
FUNCTION public.PopContacts(varchar, int4[]).
But, I still can't use the $2 or the ListOfNumbers alias in the function
body. I get this error: "Unable to identify an operator '=' for
types 'integer' and 'integer[]' You will have to retype this query using an
explicit cast"
Any thoughts?
--
---------- Original Message -----------
From: "Derrick Betts" <derrick@grifflink.com>
To: <pgsql-novice@postgresql.org>
Sent: Sun, 30 May 2004 23:31:17 -0600
Subject: [NOVICE] Dynamic Array into pl/pgSQL function
> 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
------- End of Original Message -------