Hi. I'm trying to write a plperl function that returns a list of ids
that I want to use in a subquery.
The function call would look like:
select * from mlist( 168.4, 55.2, 0.1);
and would return a list of integers. I've written this function,
and it returns the right list of integers, but when I use it as a
subquery, the query hangs (if I use a return type of setof integer)
or gives an error message (if I use a return type of integer[]).
I want to use "select * from mlist( 168.4, 55.2, 0.1)" in something like
select id from ctable where cmid in ( select * from mlist( 168.4,
55.2, 0.1 ) );
or
select id from ctable where cmid = ANY ( select * from mlist( 168.4,
55.2, 0.1 ) );
cmid is an integer.
-------------------------
If I do
explain select id from ctable where cmid in ( 102185, 102186,102187 );
(*where I've hard-coded the integers*), I get
QUERY
PLAN
---------------------------------------------------------------------------------------------------
Bitmap Heap Scan on ctable (cost=2293.67..271604.69 rows=77653 width=8)
Recheck Cond: (cmid = ANY ('{102185,102186,102187}'::integer[]))
-> Bitmap Index Scan on ctable_cmid_index (cost=0.00..2274.26
rows=77653 width=0)
Index Cond: (cmid = ANY ('{102185,102186,102187}'::integer[]))
(4 rows)
First I tried using the return type setof integer, but when I execute
select id from ctable where cmid in ( select * from mlist( 168.4,
55.2, 0.1 ) );
the query just seems to hang (minutes go by) and eventually I hit Ctrl-c.
The response time for
select id from ctable where cmid in ( 102185, 102186,102187 );
(*where I've hard-coded the integers*),is very fast (< 1s).
The explain above gave me the idea to try a return type of integer[], but
then I get the error message,
ERROR: operator does not exist: integer = integer[]
HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.
I also tried a return type of text and tried to cast it to integer[]
like in the
explain, but got a syntax error.
---------------------
What return type should I be using? Is there anything wrong with using
a plperl function to generate a list of integers to use in a subquery?
I'd appreciate any suggestions, help with syntax, sample plperl
functions, etc.
Thanks,
Janet
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
