Re: 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