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.

Search Discussions

Discussion Posts

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 3 | next ›
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 25, '11 at 3:38a
activeFeb 25, '11 at 3:58p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Manlin Vee: 2 posts Vibhor Kumar: 1 post

People

Translate

site design / logo © 2021 Grokbase