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=IVvJ3Y1aGoL4EcXad4lWf0zYnfpDOQayiiTRaTyncuhu302/fUBvyrtNkcRyT5qRLIUAoZsONrPPvTD0ATbXAe2JE6ibi3AV7k4hRz5JaM41v4jUqW0vzCUDdNNgcAjYOPeNjD6RdmDjOuvhh+j1Ve7XfzHEQa3n3ZEaFCPbg3g=
Message-ID: <6d8daee30607181024w74cf4673gf2f55066219dc21@mail.gmail.com>
Date: Tue, 18 Jul 2006 10:24:36 -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: <031936836C46D611BB1B00508BE7345D048DAAB8@gatekeeper.daycos.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>

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

Here's a very simple example. However, I think passing arrays of INTs
around is dirty. I wasn't able to do this without a FOR ...IN EXECUTE
statement.

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
        -- conver the array to a text string and make it LOOK like an
IN statement
        clause := in_clause;
        clause := trim(leading '{' FROM clause);
        clause := trim(trailing '}' FROM clause);

        FOR rec IN EXECUTE 'SELECT id FROM ids WHERE id 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[]);