Functions and triggers can anyone help
I'm having a problem with setting up a function and a trigger for one of me
tables. I have just started using Postgresql and I'm trying to convert my
Oracle database over to see how it runs.
Here is my Oracle Functions
Create Or Replace FUNCTION SET_USERNAME
( eventtype IN varchar2,
message IN varchar2)
RETURN varchar2 IS
username varchar2(30);
pos1 number(7);
pos2 number(7);
BEGIN
if eventtype = 'AUDIT_SUCCESS' then
pos1 := instr(message, 'User Name:') + 10;
pos2 := instr(message, 'Domain');
return substr(message, pos1, pos2-pos1);
elsif eventtype = 'AUDIT_FAILURE' then
pos1 := instr(message, 'User Name:') + 10;
pos2 := instr(message, 'Domain:');
if (pos1 = 0 or pos2 = 0) then
pos1 := instr(message, 'Account Name:') + 13;
pos2 := instr(message, 'Target Domain:');
end if;
return substr(message, pos1, pos2-pos1);
end if;
return '' ;
EXCEPTION
WHEN others THEN
return '' ;
END;
And Here is my Postgresql function but when I try to set the trigger it says
it can't find the function (it does not exist)
CREATE FUNCTION set_username4(varchar, varchar) RETURNS opaque AS '
DECLARE
eventtype varchar;
message varchar;
username varchar(30);
pos1 number(7);
pos2 number(7);
BEGIN
if eventtype = ''AUDIT_SUCCESS'' then
pos1 := instr(message, ''User Name:'') + 10;
pos2 := instr(message, ''Domain'');
return substr(message, pos1, pos2-pos1);
elsif eventtype = ''AUDIT_FAILURE'' then
pos1 := instr(message, ''User Name:'') + 10;
pos2 := instr(message, ''Domain:'');
if (pos1 = 0 or pos2 = 0) then
pos1 := instr(message, ''Account Name:'') + 13;
pos2 := instr(message, ''Target Domain:'');
end if;
return substr(message, pos1, pos2-pos1);
end if;
return '' ;
EXCEPTION
WHEN others THEN
return '' ;
END;
' LANGUAGE 'plpgsql';
Robert Stewart
Division of Communications
Office of Infrastructure Services
Governors Office for Technology
Commonwealth of Kentucky
work: 502 564 4287
cell: 502 330 5991
pager 877 543 0473
I have read through all the docs on triggers and functions.
I'm using pgadminII to setup the trigger. It says that the function I have
created does not exist. This is after you can select it. I can see the
function there. I don't know if my function is correct. It did compile and
was created but I'm not sure it is in the correct format for a trigger to be
able to use it.
This is a little different than oracle. I have no problems using this
function and trigger in Oracle. Please Help
I would like to convert over to Postgresql because what I have seen of it. I
believe it could be a lot faster then oracle in the long run. If setup
correctly
thanks
-----Original Message-----
From: Nigel J. Andrews [mailto:nandrews@investsystems.co.uk]
Sent: Wednesday, February 05, 2003 9:35 AM
To: RobertD.Stewart@mail.state.ky.us
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Functions and triggers can anyone help
On Wed, 5 Feb 2003 RobertD.Stewart@mail.state.ky.us wrote:
I'm having a problem with setting up a function and a trigger for one of
me
tables. I have just started using Postgresql and I'm trying to convert my
Oracle database over to see how it runs.
...And Here is my Postgresql function but when I try to set the trigger it
says
it can't find the function (it does not exist)
...
But how are you trying to set the trigger. You included the function
definition, which I presume is created ok but haven't looked at, where as
you've not included the command that is failing.
You're obviously fairly technical, have you checked the reference manual in
the
documentation for the correct form of create trigger?
http://developer.postgresql.org/docs/postgres/sql-createtrigger.html
--
Nigel J. Andrews
Import Notes
Resolved by subject fallback
On Wed, 5 Feb 2003 RobertD.Stewart@mail.state.ky.us wrote:
I'm having a problem with setting up a function and a trigger for one of me
tables. I have just started using Postgresql and I'm trying to convert my
Oracle database over to see how it runs.
...And Here is my Postgresql function but when I try to set the trigger it says
it can't find the function (it does not exist)
...
But how are you trying to set the trigger. You included the function
definition, which I presume is created ok but haven't looked at, where as
you've not included the command that is failing.
You're obviously fairly technical, have you checked the reference manual in the
documentation for the correct form of create trigger?
http://developer.postgresql.org/docs/postgres/sql-createtrigger.html
--
Nigel J. Andrews
I know nothing about PgAdmin-II so can't really help there. One thing you
should be aware of though is that in postgresql 7.3 functions for as triggers
have a return type of trigger not opaque. It could be that that is what is
causing your problem if you have a recent server.
On Wed, 5 Feb 2003 RobertD.Stewart@mail.state.ky.us wrote:
I have read through all the docs on triggers and functions.
I'm using pgadminII to setup the trigger. It says that the function I have
created does not exist. This is after you can select it. I can see the
function there. I don't know if my function is correct. It did compile and
was created but I'm not sure it is in the correct format for a trigger to be
able to use it.This is a little different than oracle. I have no problems using this
function and trigger in Oracle. Please Help
I would like to convert over to Postgresql because what I have seen of it. I
believe it could be a lot faster then oracle in the long run. If setup
correctlythanks
-----Original Message-----
From: Nigel J. Andrews [mailto:nandrews@investsystems.co.uk]
Sent: Wednesday, February 05, 2003 9:35 AM
To: RobertD.Stewart@mail.state.ky.us
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Functions and triggers can anyone helpOn Wed, 5 Feb 2003 RobertD.Stewart@mail.state.ky.us wrote:
I'm having a problem with setting up a function and a trigger for one of
me
tables. I have just started using Postgresql and I'm trying to convert my
Oracle database over to see how it runs.
...And Here is my Postgresql function but when I try to set the trigger it
says
it can't find the function (it does not exist)
...But how are you trying to set the trigger. You included the function
definition, which I presume is created ok but haven't looked at, where as
you've not included the command that is failing.You're obviously fairly technical, have you checked the reference manual in
the
documentation for the correct form of create trigger?http://developer.postgresql.org/docs/postgres/sql-createtrigger.html
k
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
But how are you trying to set the trigger. You included the function
definition, which I presume is created ok but haven't looked at, where as
you've not included the command that is failing.
The given function is fine as a function, but is not directly usable
as a trigger. Trigger functions take no explicit arguments --- they
receive the current row as an implicit argument. See the examples
in the manual...
regards, tom lane
RobertD.Stewart@mail.state.ky.us wrote:
I'm having a problem with setting up a function and a trigger for one of me
tables. I have just started using Postgresql and I'm trying to convert my
Oracle database over to see how it runs.
[...snip...]
And Here is my Postgresql function but when I try to set the trigger it says
it can't find the function (it does not exist)CREATE FUNCTION set_username4(varchar, varchar) RETURNS opaque AS '
You haven't provided a complete example, so it's hard to help. One thing I did
notice is that your function is ill-defined if you intend to use it for a
trigger. See:
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql-trigger.html
Quote with emphasis added:
"PL/pgSQL can be used to define trigger procedures. A trigger procedure is
created with the CREATE FUNCTION command as a function with no arguments and a
return type of TRIGGER. Note that the function must be declared with no
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
arguments even if it expects to receive arguments specified in CREATE TRIGGER
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--- trigger arguments are passed via TG_ARGV, as described below."HTH,
Joe