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

  • Vibhor Kumar at Feb 25, 2011 at 7:00 am

    On Feb 25, 2011, at 9:08 AM, Manlin Vee wrote:

    REATE 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


    Yes. Function can be call using SELECT OR if you want to ignore the Result then you can use perform.

    Thanks & Regards,
    Vibhor Kumar
    EnterpriseDB Corporation
    The Enterprise PostgreSQL Company
    Cell: +91-932-568-2279
    vibhor.kumar@enterprisedb.com
    Blog:http://vibhork.blogspot.com
  • Manlin Vee at Feb 25, 2011 at 3:58 pm
    Very well, but just to make sure I understood this, the caller must use
    either "SELECT... INTO" or "PERFORM" to call a function that returns void in
    its body, right? That is, I cannot simply call a function like in other
    languages, i.e. C, Java, or PHP--the result back from that function must be
    handled by the caller somehow.
    --
    View this message in context: http://postgresql.1045698.n5.nabble.com/Failure-to-Call-a-Nested-Function-tp3399589p3400329.html
    Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Related Discussions

Discussion Navigation
viewthread | post
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