From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Matthew Peter <survivedsushi(at)yahoo(dot)com> |
Cc: | Jaime Casanova <systemguards(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Passing a list of values to a function |
Date: | 2006-01-10 03:28:55 |
Message-ID: | 20060110032855.GA41244@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
[Please don't use a blank subject. I've added a subject to this
message.]
On Mon, Jan 09, 2006 at 03:26:45PM -0800, Matthew Peter wrote:
> How come when I pass in a list in it doesn't use it as a list of
> integers? Do I need to somehow make $1 be interpreted as a list of
> ints? Rather than just passing a text value that contains the list?
>
> CREATE TABLE my_tbl (u_id int);
>
> INSERT INTO my_tbl (u_id) values (1);
> INSERT INTO my_tbl (u_id) values (2);
> INSERT INTO my_tbl (u_id) values (3);
>
> CREATE OR REPLACE FUNCTION getlist(text) RETURNS SETOF my_tbl as $$
> SELECT * FROM my_tbl
> WHERE u_id IN (0, $1);
>
> $$ LANGUAGE SQL;
>
> SELECT * from getlist('1,2,3');
> (0 rows)
You've passed a text argument so you're getting a query plan like
this:
test=> EXPLAIN SELECT * FROM my_tbl WHERE u_id IN (0, '1,2,3'::text);
QUERY PLAN
----------------------------------------------------------
Seq Scan on my_tbl (cost=0.00..1.05 rows=2 width=4)
Filter: ((u_id = 0) OR ((u_id)::text = '1,2,3'::text))
(2 rows)
You could write the function to take an array of integers instead
of a text string:
CREATE OR REPLACE FUNCTION getlist(integer[]) RETURNS SETOF my_tbl as $$
SELECT * FROM my_tbl
WHERE u_id = 0 OR u_id = ANY($1)
$$ LANGUAGE SQL STABLE STRICT;
SELECT * FROM getlist('{1,2,3}');
Another way would be to build a query string in a PL/pgSQL function
and use EXECUTE, but beware of embedding function arguments in query
strings without quoting.
--
Michael Fuhr
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-01-10 03:40:47 | Re: calling stored procedure with array paramenter (for psql) |
Previous Message | Assad Jarrahian | 2006-01-10 03:17:17 | Re: calling stored procedure with array paramenter (for psql) |