[HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Access to current database from C-language function
Am 01.08.2011 um 21:37 schrieb Merlin Moncure: I think David is probably right and this can be handled in pure sql simply and easily (perhaps in a function, perhaps not). The SPI interface is great, but the sql and plpgsql languages are very powerful and should always be preferred over a C solution all else being equal. Thanks for all the input! I was not aware of all the available options. As I don't have time pressure at the moment, I'll investigate the different options further before deciding for one direction. I'm quite happy to see what's possible with Postgresql! :-) kind regards, Achim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Access to current database from C-language function
Am 26.07.2011 um 00:40 schrieb Florian Pflug:
On Jul25, 2011, at 22:31 , Achim Domma wrote:
Am 25.07.2011 um 14:48 schrieb Florian Pflug:
A more low-level API is provided by {heap,index}_{beginscan,endscan},
heap_{insert,update,delete} and index_insert. However, correct handling of
transactions using this API isn't easy - for example, to update a row you'd
first have to find the latest version of that row, then decide if you're
allowed to update it, and finally create a new version.
I see the problems with the second approach, but that's definitively what I
would like to do.
You're in for a lot of work, then. I still suggest that you explain your
ultimate goals before you embark on your endeavor - people might be able to
point our easier ways to achieve those.
I have tables which store two integer IDs and a floating point rank. So the
table MyTable might have these columns:
EntityID - int
PropertyID - int
Rank - float
My algorithm needs to retrieve EntityID-Rank-Pairs for some given PropertyIDs.
So I basically want to execute a select EntityID, Rank from MyTable where
PropertyID=123 oder by Rank desc. But I need to execute multiple of those
statements and I don't want to load all the data into memory, but rather
iterate over the results step by step, stopping at certain thresholds.
My algorithm is somewhat nested and contains logic which I cannot express in
SQL, but retrieving those pairs is the most basic operation I would need. Are
cursors and option too? Is there a limitation for the number of open cursors?
One call might open 100 cursors or so.
cheers,
Achim
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Access to current database from C-language function
Hi, I have read http://www.postgresql.org/docs/9.1/static/xfunc-c.html and my idea is, to write a C function which returns a set of rows. To generate the result set, I would like to access indexes directly using the information I found at http://www.postgresql.org/docs/9.1/static/indexam.html. But I don't get the idea how to glue both parts together!? Could somebody give me a starting point? How do I get a handle to the current database inside a C function? cheers, Achim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Access to current database from C-language function
Am 25.07.2011 um 14:48 schrieb Florian Pflug:
A more low-level API is provided by {heap,index}_{beginscan,endscan},
heap_{insert,update,delete} and index_insert. However, correct handling of
transactions using this API isn't easy - for example, to update a row you'd
first have to find the latest version of that row, then decide if you're
allowed to update it, and finally create a new version.
I see the problems with the second approach, but that's definitively what I
would like to do. But I'm only interested in reading, which will hopefully make
it a bit easier. Could you guide me to a starting point? Assuming my database
has a table T with an index I. How do I get access to that index?
cheers,
Achim
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
