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