Re: Dynamic Array into pl/pgSQL function

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 -------