Re: Stored procedures and out parameters - Mailing list pgsql-hackers

From Vladimir Sitnikov
Subject Re: Stored procedures and out parameters
Date
Msg-id CAB=Je-Ey9XMvwigVc5pDQ6_zUTndHCr7t3pk73aQv6YcgrGVOA@mail.gmail.com
Whole thread Raw
In response to Re: Stored procedures and out parameters  (Peter Eisentraut <peter.eisentraut@2ndquadrant.com>)
List pgsql-hackers
Peter>I am familiar with the Java {call} escape.  But I think it's pretty
Peter>useless.  You're not going to get any compatibility with anything from
Peter>it, since every SQL implementation does something different with it

Would you please be more specific?

{ call ... } converts the SQL to the database-specific way of calling a stored procedure or whatever.

For instance, in Oracle DB one can have both procedures and functions.
{ ? := call my_fun() }  is converted to something like begin :b0 := my_fun(); end;
{ call my_proc(?) } is converted to begin my_proc(:b0); end;

Then comes PostgreSQL, and says: you can't invoke functions/procedures like {call my_proc()}.
This not fun at all.

Note: it is not a speculation, but I have participated in extending OracleDB-kind-of-app to PostgreSQL, and {call ...} was used there for a good reason.

{call ...} are used in lots of places exactly for the reason to call procedures.
You don't need to correct application core, and one just needs to install the procedure/function at the database side,
then it should just work.
However, current state of PostgreSQL 11 blocks use of database-independent API.

Peter>If you want to call
Peter>a procedure (created with CREATE PROCEDURE), just invoke CALL directly
Peter>without any escape syntax.

That would truly surprise end users, and it would make application portability a pain.

Vladimir

pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Stored procedures and out parameters
Next
From: Vladimir Sitnikov
Date:
Subject: Re: Stored procedures and out parameters