Error Message

Bob Pawley <rjpawley@shaw.ca> writes:

The function name is listed under Functions as - base(int4)

Trigger functions cannot take any explicit parameters.

regards, tom lane

I'm not sure what you mean.

base(int4) is the name of the function that I want to call. It follows the
format of an example in a Postgresql book I use (or perhaps misuse).

Are you saying that I need to redo the function???

Bob
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgre General" <pgsql-general@postgresql.org>
Sent: Wednesday, October 26, 2005 2:23 PM
Subject: Re: [GENERAL] Error Message

Bob,

You cannot pass argments to trigger functions. You can to other types of
functions, but not functions used as triggers. Arguments are passed regarding
the old and new records and other built in variables regarding what kind of
operation is going on, but all of that is "unseen".

They must be created as in:
CREATE TRIGGER trig1 AFTER INSERT
ON process FOR EACH ROW
EXECUTE PROCEDURE base();
^^^^^^
Note: no argument.

On Wednesday 26 October 2005 07:24 pm, Bob Pawley saith:

Bob Pawley <rjpawley@shaw.ca> writes:

I'm not sure what you mean.

base(int4) is the name of the function that I want to call. It follows
the format of an example in a Postgresql book I use (or perhaps
misuse).

Are you saying that I need to redo the function???

You need to make it a function that takes zero arguments. Trigger
functions take their arguments in a weird way, not via the usual
mechanism. See the docs.

-Doug

On Wed, Oct 26, 2005 at 07:45:19PM -0400, Terry Lee Tucker wrote:

You cannot pass argments to trigger functions. You can to other types of
functions, but not functions used as triggers. Arguments are passed regarding
the old and new records and other built in variables regarding what kind of
operation is going on, but all of that is "unseen".

They must be created as in:
CREATE TRIGGER trig1 AFTER INSERT
ON process FOR EACH ROW
EXECUTE PROCEDURE base();
^^^^^^
Note: no argument.

You *can* pass arguments to trigger functions but it's done a little
differently than with non-trigger functions. The function must be
defined to take no arguments; it reads the arguments from a context
structure instead of in the normal way. PL/pgSQL trigger functions,
for example, read their arguments from the TG_ARGV array.

http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html
http://www.postgresql.org/docs/8.0/interactive/trigger-interface.html

Example:

CREATE TABLE foo (id integer, x integer);

CREATE FUNCTION func() RETURNS trigger AS $$
BEGIN
NEW.x := TG_ARGV[0];
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo
FOR EACH ROW EXECUTE PROCEDURE func(12345);

INSERT INTO foo (id) VALUES (1);

SELECT * FROM foo;
id | x
----+-------
1 | 12345
(1 row)

However, it's not clear if this is what Bob is trying to do. His
original attempt was:

CREATE TRIGGER trig1 AFTER INSERT
ON process FOR EACH ROW
EXECUTE PROCEDURE base(int4);

He's given what looks like a function signature instead of passing
an argument. Even if this worked, he hasn't specified what argument
should be passed. Bob, can you explain what you're trying to do?

--
Michael Fuhr

On Wed, Oct 26, 2005 at 07:00:06PM -0600, Michael Fuhr wrote:

You *can* pass arguments to trigger functions but it's done a little
differently than with non-trigger functions. The function must be
defined to take no arguments; it reads the arguments from a context
structure instead of in the normal way. PL/pgSQL trigger functions,
for example, read their arguments from the TG_ARGV array.

http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html
http://www.postgresql.org/docs/8.0/interactive/trigger-interface.html

Also

http://www.postgresql.org/docs/8.0/interactive/sql-createtrigger.html

where the documentation says

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE funcname ( arguments )

...

arguments

An optional comma-separated list of arguments to be provided to the
function when the trigger is executed. The arguments are literal
string constants. Simple names and numeric constants may be written
here, too, but they will all be converted to strings. Please check
the description of the implementation language of the trigger function
about how the trigger arguments are accessible within the function; it
may be different from normal function arguments.

--
Michael Fuhr

I have a base table called "process". Each row of this table is anchored by
a serial column labeled "fluid_id".

After data has been entered into a row in "process", I want to trigger a
row in another table labeled "specification" also with a column labeled
"fluid_id". I would like this number from "process" entered into
"specification" as an integer.

I would like this to happen after each row in "process" has satisfied the
not null requirements.

I may not be employing the language you are use to using however, I hope
this explanation is somewhat clear.

Thanks for your help.

Bob

----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Terry Lee Tucker" <terry@esc1.com>
Cc: "Postgre General" <pgsql-general@postgresql.org>
Sent: Wednesday, October 26, 2005 6:00 PM
Subject: Re: [GENERAL] Error Message

On Wed, Oct 26, 2005 at 07:47:51PM -0700, Bob Pawley wrote:

I have a base table called "process". Each row of this table is anchored by
a serial column labeled "fluid_id".

What do you mean by "anchored by"? Is fluid_id the primary key for
process? Or is fluid_id a foreign key reference to some other
table? Or do you mean something else?

After data has been entered into a row in "process", I want to trigger a
row in another table labeled "specification" also with a column labeled
"fluid_id". I would like this number from "process" entered into
"specification" as an integer.

By "trigger a row" do you mean that you want the trigger on process
to insert a new row into specification? Is the following example
close to what you're looking for?

CREATE TABLE process (fluid_id integer PRIMARY KEY);
CREATE TABLE specification (fluid_id integer NOT NULL);

CREATE FUNCTION base() RETURNS trigger AS $$
BEGIN
INSERT INTO specification (fluid_id) VALUES (NEW.fluid_id);
RETURN NULL; -- ignored in AFTER triggers
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig1 AFTER INSERT ON process
FOR EACH ROW EXECUTE PROCEDURE base();

INSERT INTO process (fluid_id) VALUES (123);
INSERT INTO process (fluid_id) VALUES (456);

SELECT * FROM process;
fluid_id
----------
123
456
(2 rows)

SELECT * FROM specification;
fluid_id
----------
123
456
(2 rows)

--
Michael Fuhr