CALL optional in PL/pgSQL
This seems to be a popular issue when porting from PL/SQL, so I'll throw
it out here for discussion. Apparently, in PL/SQL you can call another
procedure without the CALL keyword. Here is a patch that attempts to
implement that in PL/pgSQL as well. It's not very pretty.
I seem to recall that there were past discussions about this, with
respect to the PERFORM command, but I couldn't find them anymore.
Also, I think PL/SQL allows you to call a procedure with no arguments
without parentheses. I have not implemented that. I think it could be
done, but it's not very appealing.
If anyone has more details about the PL/SQL side of this, that would be
useful. What I could find is that using CALL and not using CALL appear
to be equivalent.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
0001-PL-pgSQL-Allow-calling-procedures-without-CALL-keywo.patchtext/plain; charset=UTF-8; name=0001-PL-pgSQL-Allow-calling-procedures-without-CALL-keywo.patch; x-mac-creator=0; x-mac-type=0Download
From ffd93d16fd046bf0352eec914f3ae087390370ef Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Wed, 28 Feb 2018 23:22:17 -0500
Subject: [PATCH] PL/pgSQL: Allow calling procedures without CALL keyword
For compatibility with Oracle, allow calling procedures without the CALL
keyword. So
BEGIN
someproc();
END
is the same thing as
BEGIN
CALL someproc();
END
This works as long as someproc is not a keyword.
---
src/pl/plpgsql/src/expected/plpgsql_call.out | 6 ++++-
src/pl/plpgsql/src/pl_gram.y | 34 ++++++++++++++++++++++++++--
src/pl/plpgsql/src/sql/plpgsql_call.sql | 2 ++
3 files changed, 39 insertions(+), 3 deletions(-)
diff --git a/src/pl/plpgsql/src/expected/plpgsql_call.out b/src/pl/plpgsql/src/expected/plpgsql_call.out
index e2442c603c..11288ede87 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_call.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_call.out
@@ -43,6 +43,8 @@ AS $$
BEGIN
CALL test_proc3(y);
CALL test_proc3($1);
+ test_proc3(y);
+ public.test_proc3(y);
END;
$$;
CALL test_proc4(66);
@@ -51,7 +53,9 @@ SELECT * FROM test1;
----
66
66
-(2 rows)
+ 66
+ 66
+(4 rows)
DROP PROCEDURE test_proc1;
DROP PROCEDURE test_proc2;
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 688fbd6531..3c9b3e9fce 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -1921,7 +1921,25 @@ stmt_execsql : K_IMPORT
plpgsql_push_back_token(tok);
if (tok == '=' || tok == COLON_EQUALS || tok == '[')
word_is_not_variable(&($1), @1);
- $$ = make_execsql_stmt(T_WORD, @1);
+ else if (tok == '(' &&
+ /* check for keywords that can be followed by ( */
+ ($1.quoted || (strcmp($1.ident, "explain") != 0 &&
+ strcmp($1.ident, "reindex") != 0 &&
+ strcmp($1.ident, "select") != 0 &&
+ strcmp($1.ident, "vacuum") != 0 &&
+ strcmp($1.ident, "values") != 0)))
+ {
+ PLpgSQL_stmt_execsql *new;
+
+ new = palloc0(sizeof(PLpgSQL_stmt_execsql));
+ new->cmd_type = PLPGSQL_STMT_EXECSQL;
+ new->lineno = plpgsql_location_to_lineno(@1);
+ new->sqlstmt = read_sql_stmt(psprintf("CALL %s", quote_identifier(($1).ident)));
+
+ $$ = (PLpgSQL_stmt *)new;
+ }
+ else
+ $$ = make_execsql_stmt(T_WORD, @1);
}
| T_CWORD
{
@@ -1931,7 +1949,19 @@ stmt_execsql : K_IMPORT
plpgsql_push_back_token(tok);
if (tok == '=' || tok == COLON_EQUALS || tok == '[')
cword_is_not_variable(&($1), @1);
- $$ = make_execsql_stmt(T_CWORD, @1);
+ else if (tok == '(')
+ {
+ PLpgSQL_stmt_execsql *new;
+
+ new = palloc0(sizeof(PLpgSQL_stmt_execsql));
+ new->cmd_type = PLPGSQL_STMT_EXECSQL;
+ new->lineno = plpgsql_location_to_lineno(@1);
+ new->sqlstmt = read_sql_stmt(psprintf("CALL %s", NameListToQuotedString(($1).idents)));
+
+ $$ = (PLpgSQL_stmt *)new;
+ }
+ else
+ $$ = make_execsql_stmt(T_CWORD, @1);
}
;
diff --git a/src/pl/plpgsql/src/sql/plpgsql_call.sql b/src/pl/plpgsql/src/sql/plpgsql_call.sql
index 321ed43af8..845a4d1a65 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_call.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_call.sql
@@ -49,6 +49,8 @@ CREATE PROCEDURE test_proc4(y int)
BEGIN
CALL test_proc3(y);
CALL test_proc3($1);
+ test_proc3(y);
+ public.test_proc3(y);
END;
$$;
base-commit: 51057feaa6bd24b51e6a4715c2090491ef037534
--
2.16.2
On Wednesday, February 28, 2018, Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> wrote:
I seem to recall that there were past discussions about this, with
respect to the PERFORM command, but I couldn't find them anymore.
I'm thinking you are thinking of this one.
/messages/by-id/CAFjFpReVcC+RE3WBJb2X1-O=_+ORiZggDZ-Orr0QafeuAC7k-w@mail.gmail.com
David J.
2018-03-01 5:51 GMT+01:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.com
:
This seems to be a popular issue when porting from PL/SQL, so I'll throw
it out here for discussion. Apparently, in PL/SQL you can call another
procedure without the CALL keyword. Here is a patch that attempts to
implement that in PL/pgSQL as well. It's not very pretty.
The CALL is not optional in PL/SQL - I was surprised - it is required in
some environments, and it should not be used in other (like PL/SQL)
please, fix me, if I am wrong.
SQL/PSM requires it.
I agree, so in this case, the CALL can be optional - because procedures are
called by different mechanism than functions - and there is not additional
overhead. It is not strictly necessary, because tools like ora2pg has not
any problem with procedure identification and some transformations.
But - if we allow optional CALL in PL/pgSQL, then we will have
inconsistence between PL/pgSQL and other environments, when the CALL will
be required. What is not too nice.
I seem to recall that there were past discussions about this, with
respect to the PERFORM command, but I couldn't find them anymore.Also, I think PL/SQL allows you to call a procedure with no arguments
without parentheses. I have not implemented that. I think it could be
done, but it's not very appealing.
I don't like this feature. I don't see any benefit. Different case are
functions - then users can implement some pseudovariables like
CURRENT_USER, ..
Show quoted text
If anyone has more details about the PL/SQL side of this, that would be
useful. What I could find is that using CALL and not using CALL appear
to be equivalent.--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Pavel Stehule <pavel.stehule@gmail.com> writes:
2018-03-01 5:51 GMT+01:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.com
This seems to be a popular issue when porting from PL/SQL, so I'll throw
it out here for discussion. Apparently, in PL/SQL you can call another
procedure without the CALL keyword. Here is a patch that attempts to
implement that in PL/pgSQL as well. It's not very pretty.
The CALL is not optional in PL/SQL - I was surprised - it is required in
some environments, and it should not be used in other (like PL/SQL)
I think this is an actively bad idea. It introduces an inherent ambiguity
into the grammar; for instance
PERFORM (2);
now has two valid interpretations. The only way to resolve that is with
heuristics or treating a bunch more words as reserved keywords, neither of
which are appetizing. (I didn't look to see which way Peter did it, but
his description of his patch as "not very pretty" doesn't fill me with
happiness.) And it would likely cause headaches down the road whenever
we attempt to add new syntax to plpgsql.
I think we should reject the idea.
Also, I think PL/SQL allows you to call a procedure with no arguments
without parentheses. I have not implemented that. I think it could be
done, but it's not very appealing.
I don't like this feature.
This idea is even worse in terms of the amount of syntax space it will
occupy, again for zero functional benefit.
regards, tom lane
On Fri, Mar 2, 2018 at 2:01 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Pavel Stehule <pavel.stehule@gmail.com> writes:
2018-03-01 5:51 GMT+01:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.com
This seems to be a popular issue when porting from PL/SQL, so I'll throw
it out here for discussion. Apparently, in PL/SQL you can call another
procedure without the CALL keyword. Here is a patch that attempts to
implement that in PL/pgSQL as well. It's not very pretty.The CALL is not optional in PL/SQL - I was surprised - it is required in
some environments, and it should not be used in other (like PL/SQL)
It's not required in a PL/SQLl block, see
<https://www.tutorialspoint.com/plsql/plsql_procedures.htm>
This is hardly surprising given PL/SQL's Ada roots. This is exactly
how Ada allows procedure calls.
I think this is an actively bad idea. It introduces an inherent ambiguity
into the grammar; for instancePERFORM (2);
now has two valid interpretations. The only way to resolve that is with
heuristics or treating a bunch more words as reserved keywords, neither of
which are appetizing. (I didn't look to see which way Peter did it, but
his description of his patch as "not very pretty" doesn't fill me with
happiness.) And it would likely cause headaches down the road whenever
we attempt to add new syntax to plpgsql.I think we should reject the idea.
Well, the upside would be increased Oracle compatibility. I don't
think that's worthless.
I haven't dug deeply into it, but Peter's patch didn't look
desperately ugly to me at first glance.
Even though it's a tiny patch this seems like next release material at best.
Also, I think PL/SQL allows you to call a procedure with no arguments
without parentheses. I have not implemented that. I think it could be
done, but it's not very appealing.I don't like this feature.
This idea is even worse in terms of the amount of syntax space it will
occupy, again for zero functional benefit.
If we were going to do it then we should be consistent about it and
also allow parameter-less function calls to skip the parentheses. But
anyway none of that is currently proposed so let's save the argument
for the time when it is :-)
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 27/03/18 03:00, Andrew Dunstan wrote:
On Fri, Mar 2, 2018 at 2:01 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think this is an actively bad idea. It introduces an inherent ambiguity
into the grammar; for instancePERFORM (2);
now has two valid interpretations. The only way to resolve that is with
heuristics or treating a bunch more words as reserved keywords, neither of
which are appetizing. (I didn't look to see which way Peter did it, but
his description of his patch as "not very pretty" doesn't fill me with
happiness.) And it would likely cause headaches down the road whenever
we attempt to add new syntax to plpgsql.I think we should reject the idea.
Well, the upside would be increased Oracle compatibility. I don't
think that's worthless.I haven't dug deeply into it, but Peter's patch didn't look
desperately ugly to me at first glance.
I don't much like this either. The ambiguity it introduces in the
grammar is bad. I'll mark this as rejected in the commitfest.
- Heikki