CODEWITHSUNDEEP
postgresql
ALx ALx
ALx ALx

Reputation: 111

Postgresql - Function for calculating age

I am having a bit of trouble with building a function in Postgresql for calculating age of a row.

Here is my table:

CREATE TABLE students (
    id serial PRIMARY KEY, 
    name varchar(30) NOT NULL, 
    aproven boolean DEFAULT false, 
    enrolled integer DEFAULT 0, 
    birth_date DATE
);

I am trying to get a simple output from a function which should calculate the age of a student. Something like 

SELECT student_age(3);

which should calculate the age of 3rd student and returns the his unique id and his age.

Currently I am trying this:

SELECT id, age(students.birth_date::timestamp) AS years_old FROM students;

Which works but I can't seem to make it work inside a function. Can you please give me an idea?

Thank you!

Upvotes: 2

Views: 480

Answers (1)

clemens
clemens

Reputation: 17721

You might create a function which returns a specific type in SQL:

CREATE TYPE student_age AS (
    id INTEGER,
    age INTERVAL
);

CREATE OR REPLACE FUNCTION student_age(INTEGER) RETURNS student_age AS $$
    SELECT id, AGE(birth_date) AS age FROM students WHERE id = $1; 
$$ LANGUAGE SQL;

You may now use the function like a table:

SELECT * FROM student_age(3);

Upvotes: 1

Related Questions