Thread: converting Oracle to postgres

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/ !

On Fri, 2003-02-07 at 08:58, will trillich wrote:
> 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.
...
> >     elsif eventtype = 'AUDIT_FAILURE' then
>
>     else
>         if eventtype = ''AUDIT_FAILURE'' then

ELSIF is valid pg/sql - it saves an extra END IF

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "But the LORD is in his holy temple; let all the earth
      keep silence before him."           Habakkuk 2:20

will trillich <will@serensoft.com> writes:
> i think plpgsql is standard these days, so you should be able to
> create procedures without having to "install" or "activate" it.

No, you still do need to do 'createlang' to set it up.  There's been
debate about whether to make it standard.  A paranoid DBA might not
*want* PLs available.

            regards, tom lane

At 09:58 AM 2/7/03 -0500, Tom Lane wrote:

>will trillich <will@serensoft.com> writes:
> > i think plpgsql is standard these days, so you should be able to
> > create procedures without having to "install" or "activate" it.
>
>No, you still do need to do 'createlang' to set it up.  There's been
>debate about whether to make it standard.  A paranoid DBA might not
>*want* PLs available.

Yes.

Don't want a repeat of MS SQL Server. I'm sure there are people who need to
format c:\, dir, etc using MS SQL. But most people don't need this stuff
installed by default.

It might not be so bad at the start if you install a language by default.
But if the language/features get extended you could end up with a lose-lose
situation.

Regards,
Link.

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> At 09:58 AM 2/7/03 -0500, Tom Lane wrote:
>> No, you still do need to do 'createlang' to set it up.  There's been
>> debate about whether to make it standard.  A paranoid DBA might not
>> *want* PLs available.

> Yes.

> Don't want a repeat of MS SQL Server. I'm sure there are people who need to
> format c:\, dir, etc using MS SQL. But most people don't need this stuff
> installed by default.

Well, we'd certainly never install any untrusted languages by default.
The trusted languages don't let you do anything you couldn't do anyway,
given that you have SQL command access.

Nonetheless, paranoia is a good thing.

            regards, tom lane

> Here is the error I get when trying to create the trigger
> ERROR:  CreateTrigger: function set_username1() does not exist
> How do I create a function that can be seen by a trigger command.
> When I create my function I get no errors and I'm able to see the
> function.
>
>
> Here is my sql command to create my trigger
>
> CREATE TRIGGER trg_setuser1
> BEFORE INSERT
> ON eventlogs
> FOR EACH ROW
> EXECUTE PROCEDURE set_username1 ("
>   if (username is NULL) then
>     select (eventtype, message) into username from dual;
>   end if;
> END;");
>

It looks to me like your call to set_username1 in the trigger has only a
single argument, whereas the definition of the stored procedure
set_username1 has two arguments. I think PostgreSQL considers them
different functions if the argument list does not match.

>
>
>> Here is a copy of my function in oracle. ...
>
> Have you RT(Fine)M? (In particular section 19.11. "Porting from Oracle
> PL/SQL"). After you have read that, then what specifically is the
> difficulty you are having?

I apologize for being a bit of a smart-ass, but it wasn't readily
apparent to me from your original post that you had tried or read
anything to solve the problem yourself.

Regards,
Berend