programming in pgsql
Hi Pgsql,
Could anyone please advise whether the following program can be implemented
using pgsql cursors/anythign else (or do we need some external scripts)?If
yes, could you give please some function names etc?
select A from Bcluster where pvalue = 0.3
--say we get A=8
select B, A_child from CCluster where A = 8
--If we get a A_child (say we get A_child=7) from the above query we search
that value in CCluster table again and store the Bs in some place as:
select B, A_child from CCluster where A = 7
--We do this till we get all Bs and no more A_childs
--We display and store the Bs for further usage
Thanks,
AK
Angshu Kar wrote:
Hi Pgsql,
Could anyone please advise whether the following program can be implemented
using pgsql cursors/anythign else (or do we need some external scripts)?If
yes, could you give please some function names etc?
[snip series of queries]
Any procedural language will be able to handle this.
The only problem you'll have will be that the whole function will take
place inside a single transaction, so you won't be able to spread the
workload out over time.
--
Richard Huxton
Archonet Ltd
On Mon, Jan 09, 2006 at 10:13:12AM +0000, Richard Huxton wrote:
Angshu Kar wrote:
Hi Pgsql,
Could anyone please advise whether the following program can be implemented
using pgsql cursors/anythign else (or do we need some external scripts)?If
yes, could you give please some function names etc?[snip series of queries]
Any procedural language will be able to handle this.
The only problem you'll have will be that the whole function will take
place inside a single transaction, so you won't be able to spread the
workload out over time.
There are also other ways to do this. Celko presents one in "SQL For
Smarties" based on setting up a graph (google:sql for smarties graph),
and there is also contrib/ltree. Either of these is likely to perform
much better than a heirarchy, unless you're doing a lot of
inserts/updates/deletes (graphs) or need a lot of levels (ltree).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461