Re: converting Oracle to postgres - Mailing list pgsql-general

On Mon, Feb 03, 2003 at 02:05:01PM -0500, RobertD.Stewart@mail.state.ky.us wrote:
> Here is a copy of my function in oracle.
>
> Create Or Replace FUNCTION SET_USERNAME
>   ( eventtype IN varchar2,
>     message IN varchar2)

    /*
>   RETURN  varchar2 IS
    */

    returns varchar as '

[function body is a single-quoted string, so all single quotes
in the body will have to be doubled -- '' (not ")]

    declare

> username varchar2(30);
> pos1  number(7);
> pos2 number(7);
> BEGIN
>     if eventtype = 'AUDIT_SUCCESS' then

    -- don''t forget to double-your-single-quotes:
    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

    else
        if 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; -- "audit failure"

>     end if;
>     return '' ;

not sure what this exception clause does...

    /*

> EXCEPTION
>    WHEN others THEN
>        return '' ;

    */

> END;

    ' -- end of function-source string
    language 'plpgsql';

>
> How do I get it to work in postgres
>
> Please help me with the SQL commands

i think plpgsql is standard these days, so you should be able to
create procedures without having to "install" or "activate" it.

note -- this is untested, off the top of my head and is likely
to blow your timing chain. proceed at your own risk. not
available in all areas. offer good while supplies last. yada
yada.

you can (and should) double-check my work yourself, by perusing
http://postgresql.org/users-lounge/docs/7.2/postgres/plpgsql.html

(depending on the version you're using, of course)

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !