Thread: Functions and triggers can anyone help

Functions and triggers can anyone help

From
RobertD.Stewart@mail.state.ky.us
Date:

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

 

 

Re: Functions and triggers can anyone help

From
"Nigel J. Andrews"
Date:
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


Re: Functions and triggers can anyone help

From
RobertD.Stewart@mail.state.ky.us
Date:
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

Re: Functions and triggers can anyone help

From
"Nigel J. Andrews"
Date:

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


Re: Functions and triggers can anyone help

From
Tom Lane
Date:
"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

Re: Functions and triggers can anyone help

From
Joe Conway
Date:
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