Thread: 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
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