Обсуждение: 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 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

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:
> 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 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
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

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: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

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