From: | Manlin Vee <maps(dot)this(dot)address(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Failure to Call a Nested Function |
Date: | 2011-02-25 03:38:47 |
Message-ID: | 1298605127998-3399589.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-02-25 05:10:41 | Re: Enabling SSL fails to restart server |
Previous Message | David Patricola | 2011-02-24 19:15:10 | Enabling SSL fails to restart server |