Calculating age with currval in postgreSQL

Question

I have created a database with a People table which contains all the information for people such as their first name, last name, gender,date of birth, and email. Each row in this table has a serial peopleID. I am trying to create a stored procedure and trigger to automatically calculate the age of a person that was just entered. I am trying to use currval to get the last inserted peopleID.

This is my stored procedure:

CREATE OR REPLACE FUNCTION PersonAge (peopleID INT)
RETURNS INTERVAL AS
$$
DECLARE
birthday date := (SELECT People.dateOfBirth
        FROM People
        );
BEGIN
RETURN age(birthday);
END;
$$
LANGUAGE plpgsql

This is my trigger:

CREATE TRIGGER CalculateAge
AFTER INSERT OR UPDATE ON People
FOR EACH ROW
EXECUTE PROCEDURE PersonAge( SELECT currval(‘people_id_seq’));

This is the error I am getting when I try to implement it with an insert statement:

ERROR:  syntax error at or near "currval"
LINE 5: EXECUTE PROCEDURE PersonAge( SELECT currval('people_id_seq')...
                                        ^
********** Error **********

ERROR: syntax error at or near "currval"
SQL state: 42601
Character: 116

I think I am a little confused as to what specific elements I need here. I am fairly new to triggers. — user5959738, Dec 06 '16 at 20:27
sure thing. I picked up docs for you to read - look in my answer — Vao Tsun, Dec 06 '16 at 20:30

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

You pass arguments in not supported way, please read docs

Note that the function must be declared with no arguments even if it expects to receive some arguments specified in CREATE TRIGGER — such arguments are passed via TG_ARGV, as described below.

and

TG_ARGV[]

Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.

(emphasis mine)

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 06 '16 at 20:12

Vao Tsun

47,234
13
100
132

This would make available in TG_ARGV the value of `currval(‘people_id_seq’)` when you issued `CREATE TRIGGER CalculateAge`, not when it was executed on `INSERT` or `UPDATE`. I have no idea what the author wants to do though. – Evan Carroll Dec 07 '16 at 00:11

Calculating age with currval in postgreSQL

1 Answers1