Problem with PostgreSQL Query with Function (T_FUNCTION error)

I installed PostgreSQL just a few days ago, so I’m still a newbie, and I don’t have much experience with functions, either. So I can’t figure out to write this hierarchical query.

It isn’t clear to me if I’m supposed to start with this simple query or not:

select * from gz_life;

This is a function to translate parent_id into taxonomic rank name:

create function tax_rank(id integer) returns text as $$
    select case id
             when 1 then 'Class'
             when 2 then 'Order'
             when 3 then 'Family'
             when 4 then 'Genus'
             when 5 then 'Species'
           end;
$$ language sql;

This is the query:

with recursive hier(taxon,parent_id) as (
  select m.taxon, null::integer
  from   gz_life m
  where  taxon='Mammalia' --<< substitute me
  union all
  select m.taxon, m.parent_id
  from   hier, gz_life m
  where  m.parent=hier.taxon
)
select tax_rank(parent_id),
       count(*) num_of_desc
from   hier
where  parent_id is not null
group by parent_id
order by parent_id;

So, putting it all together, I get two possible scripts…

// FUNCTION
create function tax_rank(id integer) returns text as $$
    select case id
             when 1 then 'Class'
             when 2 then 'Order'
             when 3 then 'Family'
             when 4 then 'Genus'
             when 5 then 'Species'
           end;
$$ language sql;

// QUERY
$sql = "with recursive hier(taxon,parent_id) as (
  select m.taxon, null::integer
  from gz_life m
  where taxon='Mammalia' --<< substitute me
  union all
  select m.taxon, m.parent_id
  from hier, gz_life m
  where m.parent=hier.taxon
)
select tax_rank(parent_id),
       count(*) num_of_desc
from   hier
where  parent_id is not null
group by parent_id
order by parent_id;";

pg_free_result($result);
pg_close($dbh);

Or this (beginning with a general query of gz_life)…

// FIRST QUERY
$sql = "select * from gz_life;";

// FUNCTION
create function tax_rank(id integer) returns text as $$
    select case id
             when 1 then 'Class'
             when 2 then 'Order'
             when 3 then 'Family'
             when 4 then 'Genus'
             when 5 then 'Species'
           end;
$$ language sql;

// SECOND QUERY
$sql = "with recursive hier(taxon,parent_id) as (
  select m.taxon, null::integer
  from gz_life m
  where taxon='Mammalia' --<< substitute me
  union all
  select m.taxon, m.parent_id
  from hier, gz_life m
  where m.parent=hier.taxon
)
select tax_rank(parent_id),
       count(*) num_of_desc
from   hier
where  parent_id is not null
group by parent_id
order by parent_id;";

pg_free_result($result);
pg_close($dbh);

Either way, I get a T_FUNCTION error on “Create function.” Can someone show me how to write this? Thanks.

you’re trying to execute SQL code as PHP code. additionally you never pass these queries to PostgreSQL.

Yikes. Sounds like I need to go back to square one.

I’ll post a new question asking how to do a hierarchical query. Thanks.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.