Skip site navigation (1) Skip section navigation (2)

Re: Access to current database from C-language function

From: Achim Domma <domma(at)procoders(dot)net>
To: Florian Pflug <fgp(at)phlo(dot)org>
Cc: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Access to current database from C-language function
Date: 2011-08-01 11:23:26
Message-ID: 55A95A5E-67C4-4ADF-B776-2DC3F93E7B31@procoders.net (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2011-08-01 11:25:59
Subject: Re: Hot standby and GiST page splits (was Re: WIP: Fast GiST index build)
Previous:From: Thom BrownDate: 2011-08-01 10:47:18
Subject: Re: Hot standby and GiST page splits (was Re: WIP: Fast GiST index build)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group