Forum OpenACS Development: Quick get_parents function

Collapse
Posted by David Walker on
I've written the following function to recursively get the parents of an object_id. Unfortunately postgresql 7.1 will not accept a recursive sql query without hacking.

(The next version of Postgres released may support recursive sql queries directly or if not directly then using the CREATE OR REPLACE functionality as I'm imitating here)

Here is the function but PG will not accept this.

CREATE FUNCTION "get_parents" (integer,integer) RETURNS SETOF 
integer AS '
select object_id from acs_objects where object_id= $1
union
select get_parents(context_id,$2 -1) from acs_objects where 
object_id = $1 and
($2 -1) > 0
' LANGUAGE 'sql' WITH ( isstrict );


Here is the create_or_replace hack. It requires pltcl and will not work with overloaded functions (i.e. get_parents(int) and get_parents(int,int) as 2 functions. it can't tell which one to replace)

CREATE FUNCTION "create_or_replace" (character 
varying,character varying,character varying,character 
varying,character varying) RETURNS character varying AS '
 set func_name $1
 set func_args $2
 set func_returns $3
 set func_definition $4
 set func_language $5

# DO NOT USE THIS FUNCTION ON OVERLOADED PROCS
# IT WILL FAIL AND RUIN THEM CAUSING THEM TO REQUIRE
# DROPPING AND RECREATING

spi_exec "select 1 as proc_exists from pg_proc where proname= 
''${func_name}''"

if {${proc_exists} == 1 } {
 set create_function "create function ${func_name}_newcor "
} else {
 set create_function "create function ${func_name} "
}
append create_function " ${func_args}
 returns ${func_returns} as ''${func_definition}''
language ''${func_language}''"

spi_exec ${create_function}

if {${proc_exists} != 1} {
 return ok
}

spi_exec "select oid from pg_proc where
 proname=''${func_name}'' and proargtypes = 
 (select proargtypes from pg_proc where proname=
 ''${func_name}_newcor'')"

spi_exec "update pg_proc set prosrc=newsrc, probin=newbin
 from (select prosrc as newsrc, probin as newbin from pg_proc
  where proname = ''${func_name}_newcor'') myalias
 where oid = $oid"

spi_exec "drop function ${func_name}_newcor${func_args}"

return ${oid}
' LANGUAGE 'pltcl';


and here is the syntax for creating the get_parents function using the above hack.

# create the function first so the oid will exist so
# the function can refer to itself by oid
CREATE FUNCTION "get_parents" (integer,integer) RETURNS SETOF 
integer AS '
select object_id from acs_objects where object_id= 1'
language 'sql';

select create_or_replace('get_parents','(int,int)','int','
select object_id from acs_objects where object_id= $1
union
select get_parents(context_id,$2 -1) from acs_objects
	where object_id = $1 and  ($2 -1) > 0
','sql');


and here is a sample query using this function. "6319" is the object_id in question and "5" is the maximum number of layers up to go

select acs_object__name(o.object_id),*
 from acs_objects o inner join (select get_parents(6319,5) as 
object_id ) gp
  on o.object_id = gp.object_id


I figure this should be a fast way to return all parents of an object and should be easily modifiable to use a different hierarchy if necessary.

Let me know what you think.