plperl function

From: Janet Jacobsen <jsjacobsen(at)lbl(dot)gov>
To: pgsql-general(at)postgresql(dot)org
Subject: plperl function
Date: 2009-08-13 22:26:09
Message-ID: 4A849301.5080507@lbl.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Daniel Verite 2009-08-13 22:44:41 Re: comparing NEW and OLD (any good this way?)
Previous Message Sam Mason 2009-08-13 22:20:26 Re: max_allowed_packet equivalent in Postgres?