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,
--
gurjeet[[email protected]
EnterpriseDB http://www.enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
create table rhnAction( id numeric not null, prerequisite numeric );
insert into rhnAction values( 1, null );
insert into rhnAction values( 2, 1 );
insert into rhnAction values( 3, 1 );
insert into rhnAction values( 4, null );
insert into rhnAction values( 5, 4 );
insert into rhnAction values( 6, 2 );
insert into rhnAction values( 7, 6 );
insert into rhnAction values( 8, 3 );
create or replace function rhn_get_action_prerequisites_recurse( p_id rhnAction.id%type,
p_mgr rhnAction.prerequisite%type,
p_level int)
returns setof record as
$$
declare
rec record;
rec1 record;
begin
select id, prerequisite, 0 as level into rec from rhnAction where false;
rec.id = p_id;
rec.prerequisite = p_mgr;
rec.level = p_level;
return next rec;
for rec in select id, prerequisite from rhnAction where prerequisite = p_id loop
for rec1 in select id, prerequisite, level from rhn_get_action_prerequisites_recurse( rec.id, rec.prerequisite, p_level + 1 ) as f( id numeric, prerequisite numeric, level int ) loop
return next rec1;
end loop;
end loop;
return;
end;
$$ language plpgsql;
create or replace function rhn_get_action_prerequisites( p_id rhnAction.id%type )
returns setof record as
$$
declare
rec record;
rec1 record;
begin
for rec in select id, prerequisite from rhnAction where id = p_id loop
for rec1 in select id, prerequisite, level from rhn_get_action_prerequisites_recurse( rec.id, rec.prerequisite, 1 ) as f( id numeric, prerequisite numeric, level int ) loop
return next rec1;
end loop;
end loop;
return;
end;
$$ language plpgsql;
select lpad( id, level*2 ), prerequisite from rhn_get_action_prerequisites( 1 ) as f( id numeric, prerequisite numeric, level int);
select lpad( id, level*2 ), prerequisite from rhn_get_action_prerequisites( 2 ) as f( id numeric, prerequisite numeric, level int);
select lpad( id, level*2 ), prerequisite from rhn_get_action_prerequisites( 4 ) as f( id numeric, prerequisite numeric, level int);
_______________________________________________
Spacewalk-devel mailing list
[email protected]
https://www.redhat.com/mailman/listinfo/spacewalk-devel
