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