A function calculating average age in years in SQL [postgresql]
average, pgadmin, plpgsql, postgresql, sql
Solution
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
Problem
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.