trouble with triggers

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.

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

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

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

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

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

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