Hi,
I'm trying to create a simple trigger and use a bit of refactoring to
modularize a common section of functionality into another function. In
process, I have created two functions, caller (invoked by the trigger) and
callee (invoked by the caller which it [callee] returns void). However, I'm
getting an error message that is not very descriptive. Here's my code:
CREATE TABLE Person (
-- other attributes
date_of_birth DATE NOT NULL;
);
CREATE OR REPLACE FUNCTION check_year(IN _year INTEGER)
RETURNS void AS $$
DECLARE
current_year INTEGER;
BEGIN
current_year := (SELECT EXTRACT(year from now()));
IF _year > current_year THEN
RAISE EXCEPTION 'Year cannot be in the future date: %', current_year;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION check_billionaire_age()
RETURNS TRIGGER AS $check_age$
DECLARE
dml_year INTEGER;
BEGIN
dml_year := (SELECT EXTRACT(year from NEW.date_of_birth));
check_year(dml_year); -- this is the culprit, line 20
RETURN NEW;
END;
$check_age$ LANGUAGE plpgsql; -- line 25
CREATE TRIGGER check_age
BEFORE INSERT OR UPDATE ON Person
FOR EACH ROW EXECUTE PROCEDURE check_age(); -- line 30
The error that I get is as following:
CREATE FUNCTION
psql:/path/to/my.sql:(line 25): ERROR: : syntax error at or near
"check_year"
LINE 1: check_year( $1 )
^
QUERY: check_year( $1 )
CONTEXT: SQL statement in PL/PgSQL function "check_age" near line 5
psql:/path/to/my.sql:(line 30): ERROR: function check_age() does not exist
Interestingly, if I append "PERFORM" before line "20" to invoke
check_year(), the error message subdues. What am I doing wrong? Do I have to
use PERFORM func() when the callee returns "void" to discard the result?
Thanks.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Failure-to-Call-a-Nested-Function-tp3399589p3399589.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
