Re: Error Message - Mailing list pgsql-general

From Bob Pawley
Subject Re: Error Message
Date
Msg-id 001601c5daa0$d2e717f0$ac1d4318@OWNER
Whole thread Raw
In response to Error Message  (Bob Pawley <rjpawley@shaw.ca>)
Responses Re: Error Message
List pgsql-general
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: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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org


pgsql-general by date:

Previous
From: "Cristian Prieto"
Date:
Subject: Re: Variable return type...
Next
From: Bruce Momjian
Date:
Subject: Re: escape string type for upcoming 8.1