Horton

Thread

  1. converting Oracle to postgres

    robertd.stewart@mail.state.ky.us — 2003-02-03T19:05:01Z

    Here is a copy of my function in oracle.

 

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;

 

 

How do I get it to work in postgres

Please help me with the SQL commands

 

Thanks

 

 

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

  2. Re: converting Oracle to postgres

    Will Trillich <will@serensoft.com> — 2003-02-07T08:58:34Z

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

  3. Re: converting Oracle to postgres

    Oliver Elphick <olly@lfix.co.uk> — 2003-02-07T14:10:15Z

    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

  4. Re: converting Oracle to postgres

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-02-07T14:58:13Z

    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

  5. Re: converting Oracle to postgres

    Lincoln Yeoh <lyeoh@pop.jaring.my> — 2003-02-07T15:27:29Z

    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.

  6. Re: converting Oracle to postgres

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-02-07T15:35:42Z

    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