Re: Stored Procedure examples

Peter Eisentraut wrote:
> Dave Page wrote:
>> pgAdmin defines a stored procedure as:
>>
>> - A function on EnterpriseDB 8.0 or above, written in edbspl.
>
> Why does EnterpriseDB determine what is a stored procedure in
> PostgreSQL? Shouldn't that be limited to their own version of pgAdmin?

The community version of pgAdmin has always supported EnterpriseDB
features. Are you trying to say we should not do that?

And of course, EDB DevStudio does also support SPs (and conversely,
community PostgreSQL).

>> - A function written in EnterpriseDB or PostgreSQL 8.1 or above with
>> OUT or INOUT arguments.
>
> What sense does that make?

The spec says that a procedure may have IN, OUT and INOUT parameters,
whilst a function only has IN parameters.

It also says that a procedure doesn't return a value whereas a function
does.

Because PostgreSQL allows return values and IN/OUT/INOUT parameters on
the same routine, we use the first part of the definition only when
making our distinction.

Source: section 4.27, SQL-invoked Routines in SWD-02-Foundation-2003-09

>> The separation (in pgAdmin) is historic, and comes from EnterpriseDB
>> which treats the two object types more distinctly for Oracle
>> compatibility (and had SPs before PostgreSQL).
>
> The SQL standard does define procedures distinct from functions, and we
> might want to add that someday, so that system isn't going to make
> sense anymore.

Eh? the spec defines them separately, as does pgAdmin, so if PostgreSQL
does it as well pgAdmin's system will no longer make sense? Sure we
might have to change how the two are distinguished in future versions,
but that's no different from other changes we have to make from version
to version. I doubt it's likely to be anything like as much work as the
operator family reshuffle has been.

Regards, Dave.