Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group