Re: Passing tabular data around using python functions

Hi,

I'm just trying to figure out what's possible with Postgresql and Python. One thing that's important for me, would be to pass result sets around to process them further. I have a table like this:

create table fps (
docid integer,
conceptid integer,
rank float4
)

And the following function:

create or replace function vectormatch(data fps[])
returns table(docid integer, weigth float4)
as $$
plpy.notice(type(data))
plpy.notice(data)
...
$$ language plpythonu;

I call the function like this:

select * from vectormatch(array(select (docid,conceptid,rank)::fps from fps where docid = 4205591))

and get the following output:

NOTICE: <type 'list'>
CONTEXT: PL/Python function "vectormatch"
NOTICE: ['(4205591,1,1)', '(4205591,1219,1)', ...]
CONTEXT: PL/Python function "vectormatch"

I'm quite surprised that there are strings in the list and not tuples!? I tried my best, but I have no idea what I might be doing wrong. The main purpose of my sample/experiment is, to pass the results of a query to a function and to process it there. Any hint would be very appreciated.

cheers,
Achim

On 30/07/12 14:33, Achim Domma wrote:
> Hi,

Hi Achim,

this list is meant for discussing the development of PostgreSQL, in the
future you might want to ask your question on pgsql-general.

However, to answer your question:

> I call the function like this:
>
> select * from vectormatch(array(select (docid,conceptid,rank)::fps from fps where docid = 4205591))
>
> and get the following output:
>
> NOTICE:<type 'list'>
> CONTEXT: PL/Python function "vectormatch"
> NOTICE: ['(4205591,1,1)', '(4205591,1219,1)', ...]
> CONTEXT: PL/Python function "vectormatch"
>
> I'm quite surprised that there are strings in the list and not tuples!? I tried my best, but I have no idea what I might be doing wrong. The main purpose of my sample/experiment is, to pass the results of a query to a function and to process it there. Any hint would be very appreciated.

Yes, it's a missing feature of PL/Python, but in your case you could
work around it by writing your function like this:

create or replace function vectormatch(docid integer[], conceptid
integer[], rank float4[])
returns table(docid integer, weigth float4)
as $$
data = zip(docid, conceptid, rank)
plpy.notice(data)
...
$$ language plpythonu;

and then calling it like this:

select vectormatch(array_agg(docid), array_agg(conceptid),
array_agg(rank)) from fps where docid = 4205591;

Cheers,
Jan