By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,787 Members | 1,379 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,787 IT Pros & Developers. It's quick & easy.

plpgsql trigger function with arguments

P: n/a
I'm sure I saw something like this on the postgresql web
site but the the search function is down in the documentation
area.

I'm unable to pass a function arguments in a CREATE
TRIGGER statement.

What am I doing wrong here?

CREATE FUNCTION pregs_func (VARCHAR(15))
RETURNS trigger
LANGUAGE plpgsql
AS '
DECLARE
trigger_type ALIAS FOR $1;

BEGIN
IF trigger_type = ''insert'' THEN
...
END IF;
END;
';

CREATE TRIGGER pregs_insert_trigger
AFTER INSERT
ON pregs FOR EACH ROW
EXECUTE PROCEDURE pregs_func('insert');

CREATE TRIGGER pregs_update_trigger
AFTER UPDATE
ON pregs FOR EACH ROW
EXECUTE PROCEDURE pregs_func('update');

The CREATE TRIGGER statements return:
ERROR: CreateTrigger: function pregs_func() does not exist
I tried doing variations on:
create trigger pregs_insert_trigger after insert on pregs
for each row execute procedure
pregs_func (cast('insert' as varchar(15)));

thinking that I don't have the right function because the
datatypes don't match, but I get:
ERROR: parser: parse error at or near "cast" at character 106

PostgreSQL 7.3.

Thanks,

Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
hello

You have to write functions without params. Params for triggers are
accessed not via function params, but via special variable TG_ARGV[]

http://archives.postgresql.org/pgsql...3/msg01021.php

regards
Pavel Stehule

On Tue, 2 Mar 2004, Karl O. Pinc wrote:
I'm sure I saw something like this on the postgresql web
site but the the search function is down in the documentation
area.

I'm unable to pass a function arguments in a CREATE
TRIGGER statement.

What am I doing wrong here?

CREATE FUNCTION pregs_func (VARCHAR(15))
RETURNS trigger
LANGUAGE plpgsql
AS '
DECLARE
trigger_type ALIAS FOR $1;

BEGIN
IF trigger_type = ''insert'' THEN
...
END IF;
END;
';

CREATE TRIGGER pregs_insert_trigger
AFTER INSERT
ON pregs FOR EACH ROW
EXECUTE PROCEDURE pregs_func('insert');

CREATE TRIGGER pregs_update_trigger
AFTER UPDATE
ON pregs FOR EACH ROW
EXECUTE PROCEDURE pregs_func('update');

The CREATE TRIGGER statements return:
ERROR: CreateTrigger: function pregs_func() does not exist
I tried doing variations on:
create trigger pregs_insert_trigger after insert on pregs
for each row execute procedure
pregs_func (cast('insert' as varchar(15)));

thinking that I don't have the right function because the
datatypes don't match, but I get:
ERROR: parser: parse error at or near "cast" at character 106

PostgreSQL 7.3.

Thanks,

Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #2

P: n/a
On Tue, 02 Mar 2004 21:35:27 -0600, Karl O. Pinc wrote:

<..>>
CREATE TRIGGER pregs_insert_trigger
AFTER INSERT
ON pregs FOR EACH ROW
EXECUTE PROCEDURE pregs_func('insert');

CREATE TRIGGER pregs_update_trigger
AFTER UPDATE
ON pregs FOR EACH ROW
EXECUTE PROCEDURE pregs_func('update');


According to the docs, this is unnecessary. A trigger function
automatically has a number of variables instantiated for it:

http://www.postgresql.org/docs/7.3/s...plpgsqltrigger

The TG_OP variable contains 'INSERT','UPDATE' or 'DELETE'.

--
Colin Fox
President
CF Consulting Inc.

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.