pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to pass array of values to a stored procedure
5+ messages / 3 participants
[nested] [flat]
* How to pass array of values to a stored procedure
@ 2006-07-18 14:26 Curtis Scheer <Curtis@DAYCOS.com>
0 siblings, 2 replies; 5+ messages in thread
From: Curtis Scheer @ 2006-07-18 14:26 UTC (permalink / raw)
To: pgsql-sql
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).
Thanks,
Curtis
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to pass array of values to a stored procedure
@ 2006-07-18 17:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Curtis Scheer <Curtis@DAYCOS.com>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-07-18 17:15 UTC (permalink / raw)
To: Curtis Scheer <Curtis@DAYCOS.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to pass array of values to a stored procedure
@ 2006-07-18 17:24 Tony Wasson <ajwasson@gmail.com>
parent: Curtis Scheer <Curtis@DAYCOS.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tony Wasson @ 2006-07-18 17:24 UTC (permalink / raw)
To: Curtis Scheer <Curtis@daycos.com>; +Cc: pgsql-sql
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[]);
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to pass array of values to a stored procedure
@ 2006-07-18 17:33 Tony Wasson <ajwasson@gmail.com>
parent: Tony Wasson <ajwasson@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tony Wasson @ 2006-07-18 17:33 UTC (permalink / raw)
To: Curtis Scheer <Curtis@daycos.com>; +Cc: pgsql-sql
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[]);
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to pass array of values to a stored procedure
@ 2006-07-18 18:37 Curtis Scheer <Curtis@DAYCOS.com>
0 siblings, 0 replies; 5+ messages in thread
From: Curtis Scheer @ 2006-07-18 18:37 UTC (permalink / raw)
To: Tony Wasson <ajwasson@gmail.com>; Curtis Scheer <Curtis@DAYCOS.com>; +Cc: pgsql-sql
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).
> >
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-07-18 18:37 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-18 14:26 How to pass array of values to a stored procedure Curtis Scheer <Curtis@DAYCOS.com>
2006-07-18 17:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-18 17:24 ` Tony Wasson <ajwasson@gmail.com>
2006-07-18 17:33 ` Tony Wasson <ajwasson@gmail.com>
2006-07-18 18:37 Re: How to pass array of values to a stored procedure Curtis Scheer <Curtis@DAYCOS.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox