Thread: plperl function

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

>
>    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.
>

Sounds like you are trying to return directly the query.

You must do a loop with that query inside (cursor) and
use next clause (to return one by one the values)
OR
 return the query directly using return query (i don't remember
right now the plperl function to do that)

CREATE OR REPLACE FUNCTION perl_func()
RETURNS SETOF INTEGER AS $$
  my $rv = spi_exec_query('select id from ctable where cmid in (
select i from mlist( 168.4,   55.2, 0.1 ) );');
   my $status = $rv->{status};
    my $nrows = $rv->{processed};
  foreach my $rn (0..$nrows -1) {
        return_next($row->{i});
    }
    return undef;
$$ LANGUAGE plperl;

SELECT * FROM perl_func();

I didn't test it, if you have problems, i'll try to help again :)

The error is telling you that could not return an array into
integer.


--
              Emanuel Calvo Franco
             Database consultant at:
                    www.siu.edu.ar
        www.emanuelcalvofranco.com.ar