Thread: How to pass array of values to a stored procedure
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Does anyone have any examples of how I would make a stored procedure in plpgsql that would allow for passinga list or arrays of values to be used in an sql </span></font>IN clause? Like so: select * from table where field1in (values).<p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt">Thanks,</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12.0pt">Curtis</span></font><font face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial"></span></font></div>
Curtis Scheer <Curtis@DAYCOS.com> writes:
> 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).
Probably the "field1 = ANY (arrayvalue)" syntax is what you want. Note
however that this can't be turned into an indexscan on field1 in existing
releases (8.2 will be able to do it).
regards, tom lane
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[]);
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[]);
Thanks Tom and Tony for the help, I actually implemented the solution using a bpchar[] and hopefully the "field1 = ANY (arrayvalue)" syntax will not hurt the query performance a lot without an index scan. Thanks again, Curtis 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). > >