Thread: trouble with triggers

trouble with triggers

From

"Robert Treat"

Date:

17 July 2001, 13:36:28

[hopefully this isnt a repost, I seem to be haveing an issue getting message
through]

I am trying to use the following trigger:

CREATE TRIGGER formatname BEFORE update OR insert ON mytable FOR EACH row
EXECUTE PROCEDURE lower(name);

however, it gives me the message:

ERROR:  CreateTrigger: function lower() does not exist

obviously this does exist, since I can do inserts/updates/selects using
lower(). I have also tried creating my own version of a lower function but
it gives me the same message.

Am I missing something? This seems like it should be pretty straightforward.
tia,

robert

Re: trouble with triggers

From

Fran Fabrizio

Date:

17 July 2001, 14:42:55

>
> CREATE TRIGGER formatname BEFORE update OR insert ON mytable FOR EACH row
> EXECUTE PROCEDURE lower(name);

The way I understand it, you can't just use any function as a trigger.  It has
to have a return type of opaque.  I see that you are basically trying to
convert to lowercase before inserting, but I'm pretty sure you'd have to define
your own pgplsql function for the trigger which uses the special variable 'new'
to access the incoming row.  Your function can call lower() of course.

Others here can probably give you more details.

-Fran

Re: trouble with triggers

From

"Mitch Vincent"

Date:

17 July 2001, 14:44:10

    You can't use aggregate function in triggers like that.... You need to
define a function that makes the name lower case

    lower(name) will return the lower case of the name, but that's it, your
trigger doesn't actually do anything... I'm not quite sure how you would do
what you want to do with a trigger, or if it's possible (I think it is but
don't have time to look up the correct syntax)... Hopefully someone can give
you a definitive yes or no on the idea. I can give a definite no on the way
you're trying it now, though...

    Check the manual, there are some examples of PLSQL functions that might
help you out.

    Good luck!

-Mitch


----- Original Message -----
From: "Robert Treat" <robertt@auctionsolutions.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, July 17, 2001 9:56 AM
Subject: [GENERAL] trouble with triggers


> [hopefully this isnt a repost, I seem to be haveing an issue getting
message
> through]
>
> I am trying to use the following trigger:
>
> CREATE TRIGGER formatname BEFORE update OR insert ON mytable FOR EACH row
> EXECUTE PROCEDURE lower(name);
>
> however, it gives me the message:
>
> ERROR:  CreateTrigger: function lower() does not exist
>
> obviously this does exist, since I can do inserts/updates/selects using
> lower(). I have also tried creating my own version of a lower function but
> it gives me the same message.
>
> Am I missing something? This seems like it should be pretty
straightforward.
> tia,
>
> robert
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: trouble with triggers

From

"Richard Huxton"

Date:

17 July 2001, 15:30:49

From: "Robert Treat" <robertt@auctionsolutions.com>

> CREATE TRIGGER formatname BEFORE update OR insert ON mytable FOR EACH row
> EXECUTE PROCEDURE lower(name);

> ERROR:  CreateTrigger: function lower() does not exist
>
> obviously this does exist, since I can do inserts/updates/selects using
> lower(). I have also tried creating my own version of a lower function but
> it gives me the same message.
>
> Am I missing something? This seems like it should be pretty
straightforward.
> tia,

You need a special function for triggers. It needs to return "opaque" type
and not take any parameters (in this case). Inside your new function you
will have something like:

BEGIN
  NEW.name := lower(NEW.name);
  RETURN NEW;
END;

Since you need to use NEW and OLD to affect what is happening during your
updates.

See the manuals for an example or http://techdocs.postgresql.org/ for
several.

- Richard Huxton

RE: trouble with triggers

From

"Robert Treat"

Date:

17 July 2001, 15:35:46

> >
> > CREATE TRIGGER formatname BEFORE update OR insert ON mytable
> FOR EACH row
> > EXECUTE PROCEDURE lower(name);
>
> The way I understand it, you can't just use any function as a
> trigger.  It has
> to have a return type of opaque.  I see that you are basically trying to
> convert to lowercase before inserting, but I'm pretty sure you'd
> have to define
> your own pgplsql function for the trigger which uses the special
> variable 'new'
> to access the incoming row.  Your function can call lower() of course.
>
> Others here can probably give you more details.
>
> -Fran

Does the function have to be written using the plpgsql language? I tried
writing my own function using just sql and it gave the same error message. I
did trying writing the function using pgplsql:

CREATE FUNCTION formatmyname() RETURNS opaque AS '
BEGIN
new.name := lower(new.name);
RETURN NEW;
END;
' LANGAUGE 'pgplsql';

but got back a message that pgplsql was an unrecognized langauge, so I'm
looking for a non pgplsql solution. If I'm spinning my wheels I will work on
getting pgplsql working, but I'm hopeing to do it with a simpler approach.

robert

Re: trouble with triggers

From

Feite Brekeveld

Date:

17 July 2001, 16:12:45

Robert Treat wrote:

> [hopefully this isnt a repost, I seem to be haveing an issue getting message
> through]

you create a function first and then a trigger calling that function.
Everything is well documented in the docs coming with postgresql.

Feite

>
>
> I am trying to use the following trigger:
>
> CREATE TRIGGER formatname BEFORE update OR insert ON mytable FOR EACH row
> EXECUTE PROCEDURE lower(name);
>
> however, it gives me the message:
>
> ERROR:  CreateTrigger: function lower() does not exist
>
> obviously this does exist, since I can do inserts/updates/selects using
> lower(). I have also tried creating my own version of a lower function but
> it gives me the same message.
>
> Am I missing something? This seems like it should be pretty straightforward.
> tia,
>
> robert
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: trouble with triggers

From

Peter Eisentraut

Date:

17 July 2001, 16:25:23

Robert Treat writes:

> CREATE TRIGGER formatname BEFORE update OR insert ON mytable FOR EACH row
> EXECUTE PROCEDURE lower(name);
>
> however, it gives me the message:
>
> ERROR:  CreateTrigger: function lower() does not exist

No function lower() exists that has an argument signature that makes it
suitable as a trigger function.  Read your definition again; it makes no
sense because the result of lower() is not used anywhere.

Check out the programmer's guide about how to make trigger functions.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

RE: trouble with triggers

From

"Thalis A. Kalfigopoulos"

Date:

17 July 2001, 17:01:23

On Tue, 17 Jul 2001, Robert Treat wrote:

> Does the function have to be written using the plpgsql language? I tried
> writing my own function using just sql and it gave the same error message. I
> did trying writing the function using pgplsql:
>
> CREATE FUNCTION formatmyname() RETURNS opaque AS '
> BEGIN
> new.name := lower(new.name);
> RETURN NEW;
> END;
> ' LANGAUGE 'pgplsql';
>
> but got back a message that pgplsql was an unrecognized langauge, so I'm
> looking for a non pgplsql solution. If I'm spinning my wheels I will work on
> getting pgplsql working, but I'm hopeing to do it with a simpler approach.

There is no 'pgplsql' language, only 'plpgsql'. Functions in sql have LANGUAGE 'sql'.

cheers,
thalis

>
> robert
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>