-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Fri, 6 Feb 2009 18:22:49 +0530 Gurjeet Singh <gurjeet.si...@enterprisedb.com> wrote:
> Hi All, > > As I mentioned in the last conference call, I could not implement > a workaround for CONNECT BY in plpgsql; that was because of a > limitation in the way plpgsql handles the pseudo datatype RECORD. > > So, I turned to implementing it as an add-on in C. But I wanted to > actually see how much the reward would be if I did that, so I > performed a better grepping of the sources than before and this is > what I saw: > > <snip> > $ find ./ -type f | grep -v \' | grep -v \.git | xargs -r grep -i > "[[:space:]*]connect[[:space:]+]by" > ./backend/server/rhnAction.py: connect by prior id = > prerequisite > ./java/scripts/explain_plan.pl:connect by prior id=parent_id; > ./java/code/scripts/src/com/redhat/rhn/scripts/ExplainPlanGenerator.java: > "connect by prior id = parent_id " + > ./monitoring/PerlModules/NP/NOT-USED/ReleaseDB/ReleaseDB.pm: > CONNECT BY prior component_class = macro_class > ./schema/util/explain.sql:connect by prior id = parent_id and > statement_id = :stmt_id > ./schema/util/explain_plan_for_queries.py: connect by prior id > = parent_id and statement_id = 'query_explain' > ./schema/spacewalk/rhnsat/packages/rhn_server.pkb: > connect by prior id = prerequisite > ./schema/spacewalk/test-universe.satellite.sql: connect by > prior id = prerequisite > ./schema/spacewalk/universe.satellite.sql: connect by > prior id = prerequisite > </snip> > > (In previous "crude" grep I had encountered 125 instances!) > > Of these, only two were the real candidates: > ./backend/server/rhnAction.py: connect by prior id = > prerequisite > ./schema/spacewalk/rhnsat/packages/rhn_server.pkb: > connect by prior id = prerequisite > > and both these instances SELECT the same data: given an action_id, > give me all the pre-requisites in order. > > So I have written two plpgsql functions to accomplish what these two > instances need. Please find the code and a few examples attached. > > Using these functions, the queries can now be written as: > > ./backend/server/rhnAction.py: > > _query_lookup_action = rhnSQL.Statement(""" > select sa.action_id, sa.status > from rhnServerAction sa, > ( > select id > from rhn_get_action_prerequisites( :action_id ) > as f( id numeric, prerequisite numeric, level > int) /**** start with id = :action_id > connect by prior id = prerequisite ****/ > ) a > where sa.server_id = :server_id > and sa.action_id = a.id > """) > > Best regards, If we modify the queries to use these functions I assume we need Oracle implementations as well? Cheers, Devan - -- Devan Goodwin <dgood...@redhat.com> Software Engineer Spacewalk / RHN Satellite Halifax, Canada 650.567.9039x79267 -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.9 (GNU/Linux) iEYEARECAAYFAkmMQpIACgkQAyHWaPV9my6HEACfaYg5KPAjsI6f0cd+y+QrkdpB Ho4An0m5liOFAGdJkmpXROUOiwZk7Xb8 =WpQX -----END PGP SIGNATURE----- _______________________________________________ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel