Search Postgresql Archives

plperl function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux