A function calculating average age in years in SQL [postgresql]

Question

I am trying to create a plpgsql function that calculates the average age (in years) of some persons with ids (integers) stored in another table.

The code is:

begin

DROP TABLE  if EXISTS dates;
DROP TABLE  if EXISTS tmp;
DROP TABLE  if EXISTS ages;
CREATE TABLE ages (age integer);

--(...) In these lines, I create and fill the table tmp. I did not include this code
--since it's not very much related to my problem. Nevertheless, this table has only
--one integer column

CREATE TABLE dates AS (SELECT "dateofbirth" from person where "idPerson" in (select "bookedforpersonID" from personsofthistype));

UPDATE  ages  SET (age) = ((SELECT extract (year from age(dateofbirth)) from dates));

return (select avg(age) from age);

end;

Of course, dateofbirth is of type date. My problem is that the table ages that is created does not contain anything and thus I cannot return the correct result (the average age of its column). The function's return type is integer (and "Returns set" is not selected in pgadmin).

I am using PostgreSQL 9.3.4, pgAdmin III version 1.18.1.

Thank you.

Answer 1

If you are looking for average age from a person table you can do it much simpler. Something like this for starters:

 SELECT AVG(AGE(dateofbirth)) 
   FROM person