DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=Rsj/GpqozitCbkVnwpBZLrnkEbbADkGRrNxzXk+SgjXii9uYWYzJlA8TXTUBu7ir7EzrvGX4lTC2XEkRlb5RbYQXHnqfahj/KLTjBBrDbuHNYJXnxmyV4plEUcs5iywFQsTQYSxmPtK2vggJOnR5Q5NUa/kM850CeOFEurwwVz0=
Message-ID: <6d8daee30607181033g1c56b0b9x818087f6d5d4005c@mail.gmail.com>
Date: Tue, 18 Jul 2006 10:33:58 -0700
From: "Tony Wasson" <ajwasson@gmail.com>
To: "Curtis Scheer" <Curtis@daycos.com>
Subject: Re: How to pass array of values to a stored procedure
Cc: pgsql-sql@postgresql.org
In-Reply-To: <6d8daee30607181024w74cf4673gf2f55066219dc21@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <031936836C46D611BB1B00508BE7345D048DAAB8@gatekeeper.daycos.com>
	<6d8daee30607181024w74cf4673gf2f55066219dc21@mail.gmail.com>

On 7/18/06, Tony Wasson <ajwasson@gmail.com> wrote:
> On 7/18/06, Curtis Scheer <Curtis@daycos.com> wrote:
> > Does anyone have any examples of how I would make a stored procedure in
> > plpgsql that would allow for passing a list or arrays of values to be used
> > in an sql IN clause?  Like so: select * from table where field1 in (values).
> >

Ahhh... Here's an example using Tom's recommended field=ANY (arrayvalue) SQL.


CREATE TABLE ids
(
        id      INTEGER
        , PRIMARY KEY (id)
);

INSERT INTO ids VALUES (1);
INSERT INTO ids VALUES (2);
INSERT INTO ids VALUES (3);

CREATE OR REPLACE FUNCTION example_array_input(INT[]) RETURNS SETOF ids AS
$BODY$
DECLARE
        in_clause ALIAS FOR $1;
        clause  TEXT;
        rec     RECORD;
BEGIN
        FOR rec IN SELECT id FROM ids WHERE id = ANY(in_clause)
        LOOP
                RETURN NEXT rec;
        END LOOP;
        -- final return
        RETURN;
END
$BODY$ language plpgsql;

SELECT * FROM example_array_input('{1,2,4,5,6}'::INT[]);