hi
i'd like to select news from all categories being under the selected category in the tree...
when i had no time for thinking because of the deadline, i made a temporary solution
Code:
CREATE TABLE "categories" (
  "id" SERIAL, 
  "name" VARCHAR(40), 
  "level" SMALLINT DEFAULT 1 NOT NULL, 
  "parent_id" INTEGER, 
  CONSTRAINT "categories_pkey" PRIMARY KEY("id")
) WITH OIDS;
CREATE TABLE "news" (
  "id" SERIAL, 
  "category_id" INTEGER, 
  "title" TEXT, 
  "txt" TEXT,
"when_added" TIMESTAMP, 
  CONSTRAINT "news_pkey" PRIMARY KEY("id")
) WITH OIDS;
Code:
CREATE OR REPLACE FUNCTION "loadcategorychildren" (integer) RETURNS integer [] AS'
declare
q text;
arow record;
begin
toarray := '''';
q = ''select id from categories where parent_id='' || $1;
toarray := $1;
for arow in execute q loop
    toarray := toarray || '', '' || arow.id;
end loop;
return ''{'' || toarray || ''}'';
end;
'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
PHP Code:
//$cat is the root category ID
function catChildren ($cat) {
            
$q "loadcategorychildren('".$cat."')";  
         
$w $pg->selectData($q);
         
$p $w[0][0];                      
         
$children2 $cat.".";
         
$p str_replace("{"""$p);
         
$p str_replace("}"""$p);
         
$children explode (","$p);     
              if (
count($children)>1) { 
             for (
$i=1$i<count($children); $i++) {
                
$children2 .= $this->catChildren ($children[$i]);    
            }
         } 
         return 
$children2;
   } 
and then from the selected childern-categories IDs i built a very long 'where' clause to select the news, ordered by when_added.. but i don't think is a very pro solution and would like to improve it to have a ready-to-use plpgsql function, not php tricks.. do you have any idead how to build it more smart?