Hi all,

I am currently trying to estimate the effort required
to implement Oracle type stored procedure in
PostgreSQL. As I understood Oracle supports both
functions and procedures, but postgres only functions.
ALso there are no OUT parameter in postgres.
I got some info from 
http://www.compiere.org/technology/pg/porting.html

Can anyone please help me estimate the effort /
feasibility in implementing Oracle type procedures in
postgres? Any pointers are welcome.

Thanks,
Suresh


	
	
		
___________________________________________________________ALL-NEW Yahoo! Messenger - all new features - even more fun!  http://www.allnewmessenger.com

Mon, 26 Jul 2004 08:28:49 +0100 (BST)
=?iso-8859-1?q?Suresh=20Tri?= <trisuresh@yahoo.co.in>
Stored procedures - Oracle vs postgresql
Suresh Tri wrote:
> Hi all,
> 
> I am currently trying to estimate the effort required
> to implement Oracle type stored procedure in
> PostgreSQL. As I understood Oracle supports both
> functions and procedures, but postgres only functions.
> ALso there are no OUT parameter in postgres.
> I got some info from 
> http://www.compiere.org/technology/pg/porting.html
> 

Please be careful taking information from other websites as given. The 
view described there is Oracle-centric, and doesn't necessarily reflect 
PostgreSQL's need.

After a quick glance, just one aspect:
PostgreSQL indeed does not have default values for function parameters, 
but there are overloaded functions.
so instead of
function foo(bar text, nextbar text='someDefault')

you have
function foo(bar text, nextbar text)
and
function foo(bar text)
as 'select function(bar, 'someDefault')

which gives the same result. That's why PostgreSQL doesn't need default 
values.

Stored procedures:
There actually is a way to return multiple values from a function, using 
a set. So procedure foo(bar int IN, result1 text OUT, result2 text OUT)
can be converted to a function foo(bar) RETURNS record

which can be retrieved as
SELECT result1, result2 FROM foo(bar) AS prc(result1 text, result2 text)

This might be supportable by wrapping it syntactically.


Some weeks ago we had a discussion about Oracle syntax support (or 
"loadable personality" support). The thread is named "LinuxTag wrapup" 
and started July 3rd, you might want to review it.

Regards,
Andreas



Mon, 26 Jul 2004 12:58:13 +0000
Andreas Pflug <pgadmin@pse-consulting.de>
Re: Stored procedures - Oracle vs postgresql