Обсуждение: ...

Поиск
Список
Период
Сортировка

...

От
Matthew Peter
Дата:
is using IN to query a list of values in an function supported? or is there a work around for this type of query?

e.g.
...
SELECT * FROM tbl WHERE u_id IN (0$1)

$$ LANGUAGE SQL; 

Yahoo! Photos
Got holiday prints? See all the ways to get quality prints in your hands ASAP.

Re:

От
Jaime Casanova
Дата:
On 1/9/06, Matthew Peter <survivedsushi@yahoo.com> wrote:
> is using IN to query a list of values in an function supported? or is there
> a work around for this type of query?
>
> e.g.
> ...
> SELECT * FROM tbl WHERE u_id IN (0$1)
>
> $$ LANGUAGE SQL;
>

if you mean
SELECT * FROM tbl WHERE u_id IN (0, 1)

then yes

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Re:

От
Matthew Peter
Дата:
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)

I'm sure it's probably trival but I'm still learning how psql  :) Thanks

Yahoo! Photos
Got holiday prints? See all the ways to get quality prints in your hands ASAP.

Re: Passing a list of values to a function

От
Michael Fuhr
Дата:
[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

Re: Passing a list of values to a function

От
Matthew Peter
Дата:
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
Hmmmmmm .... I suspected it was using it as a text string, seeing how that's how it way defined. I guess querying it as an array would work okay.

Is there any way to blow out how the query was executed? Such as seeing all the WHERE joins and what not along with the explain output on the console? I think I read so mewhere about it showing in the logs or something?

Yahoo! Photos – Showcase holiday pictures in hardcover
Photo Books. You design it and we’ll bind it!

Re:

От
Alban Hertroys
Дата:
Matthew Peter wrote:
> 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're executing SELECT * FROM my_tbl WHERE u_id IN (0, '1,2,3').
Apparently there are no values 0 or '1,2,3' for u_id in that table.

> I'm sure it's probably trival but I'm still learning how psql  :) Thanks

You can split the string into values with string_to_array(). You'll
still be comparing ints with strings though.

Without casting the resulting strings to ints you run the risk that the
database needs to cast the int u_id value to text for every record
encountered. For 'small' data sets this shouldn't be a problem.

I suppose this is a simplified example, or you wouldn't have a reason to
use an SP; you'd just SELECT * FROM my_tbl WHERE u_id IN (0,1,2,3)

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

//Showing your Vision to the World//