postgresql是如何求年龄的_用SQL计算平均年龄的函数[postgresql](A function calculating average age in years in SQL [post...

weixin_39777488

于 2020-12-22 22:30:30 发布

阅读量761

点赞数

文章标签： postgresql是如何求年龄的

本文链接：https://blog.csdn.net/weixin_39777488/article/details/111882816

版权

本文介绍了一个在PostgreSQL中创建的plpgsql函数，用于计算与特定id关联的人员的平均年龄。通过创建并填充临时表，然后从person表中提取出生日期，并使用extract函数计算年龄。最后，函数返回平均年龄。但在实际运行中，ages表没有填充任何数据，导致无法返回正确结果。问题在于使用PostgreSQL 9.3.4和pgAdmin III 1.18.1时，ages表未正确填充和计算平均值。

摘要由CSDN通过智能技术生成

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.