Thread: PL/PgSQL "bare" function calls

I'd like to make it possible to perform function calls in PL/PgSQL
without needing to use PERFORM. I think this would be useful because (a)
it is closer to how PL/SQL works (b) PERFORM is (IMHO) a kludge, and
making it unnecessary would make programming in PL/PgSQL more natural.

Attached is a proof of concept patch that implements this. With this
patch, you can now write functions like so:

CREATE OR REPLACE FUNCTION some_func() RETURNS INT AS
'BEGIN
call_some_function();
RETURN 5;
END;
' LANGUAGE 'plpgsql';

Known issues with the patch:

(1) It seems to induce an intermittent segfault in the regression tests
on one of my machines (but not any others) which I need to fix.

(2) We should probably allow the name of a function to be double-quoted
so that it is possible to call functions with unusual names (mixed-case
and so on). I believe this should be possible, I just haven't
implemented it yet.

(3) The parser must distinguish between two cases when it sees an
unknown word (T_WORD) beginning a statement. The word could be the
beginning of a SQL statement (stmt_execsql in the grammar), such as:

UPDATE ...;

or the name of a function in a function call:

invoke_func(...);

The patch currently distinguishes between these cases by looking at the
next token -- if it is a left parenthesis, the patch assumes it is a
function call, otherwise it assumes it is a SQL statement. Is this the
best approach?

(Another method would be to teach the PL/PgSQL lexer about the basic SQL
keywords like UPDATE, INSERT, and so on, and then distinguish the two
cases that way. This would impose a maintenance burden when new SQL
commands are added, so I didn't adopt this approach.)

(4) This is proof-of-concept, so there's some mopup I still need to do
(for example, more extensive regression tests, and consider whether it
is better to reuse PLpgSQL_stmt_perform or to invent a new statement
type for this feature, update the docs, etc.)

Any comments?

-Neil

On Thu, 2004-09-16 at 00:06, Neil Conway wrote:
> Attached is a proof of concept patch that implements this.

Woops, the patch is really attached this time.

-Neil

Neil Conway <neilc@samurai.com> writes:
> (3) The parser must distinguish between two cases when it sees an
> unknown word (T_WORD) beginning a statement. The word could be the
> beginning of a SQL statement (stmt_execsql in the grammar), such as:

> UPDATE ...;

> or the name of a function in a function call:

> invoke_func(...);

> The patch currently distinguishes between these cases by looking at the
> next token -- if it is a left parenthesis, the patch assumes it is a
> function call, otherwise it assumes it is a SQL statement. Is this the
> best approach?

That seems fairly unworkable.  For example
SELECT (2,3,4);

is valid SQL.  Also I'm not sure if you can extend this to cope with
schema-qualified function names.
        regards, tom lane

Tom Lane wrote:

>Neil Conway <neilc@samurai.com> writes:
>  
>
>>(3) The parser must distinguish between two cases when it sees an
>>unknown word (T_WORD) beginning a statement. The word could be the
>>beginning of a SQL statement (stmt_execsql in the grammar), such as:
>>    
>>
>
>  
>
>>UPDATE ...;
>>    
>>
>
>  
>
>>or the name of a function in a function call:
>>    
>>
>
>  
>
>>invoke_func(...);
>>    
>>
>
>  
>
>>The patch currently distinguishes between these cases by looking at the
>>next token -- if it is a left parenthesis, the patch assumes it is a
>>function call, otherwise it assumes it is a SQL statement. Is this the
>>best approach?
>>    
>>
>
>That seems fairly unworkable.  For example
>
>    SELECT (2,3,4);
>
>is valid SQL.  Also I'm not sure if you can extend this to cope with
>schema-qualified function names.
>
>
>  
>

ISTM that this is being done at the wrong level anyway. I'd like to see 
a facility available in our SQL, e.g.
 CALL foo();

with the restriction that foo() should be declared to return void. Of 
course, that doesn't remove the keyword requirement as Neil wanted, but 
doing that would probably require a lot more work - we'd have to make 
procedures a whole lot closer to  first-class objects.

cheers

andrew

Andrew Dunstan wrote:
> ISTM that this is being done at the wrong level anyway. I'd like to see 
> a facility available in our SQL, e.g.
> 
>  CALL foo();
> 
> with the restriction that foo() should be declared to return void. Of 
> course, that doesn't remove the keyword requirement as Neil wanted, but 
> doing that would probably require a lot more work - we'd have to make 
> procedures a whole lot closer to  first-class objects.

I agree with this, except that foo() should be a PROCEDURE, not a FUNCTION.

Joe

On Thu, 2004-09-16 at 01:05, Tom Lane wrote:
> That seems fairly unworkable.  For example
> 
>     SELECT (2,3,4);
> 
> is valid SQL.

Good point. The disambiguation algorithm I suggested isn't sufficient,
but I think there ought to be _some_ reasonable algorithm.

>From glancing over the SQL commands, I believe SELECT is the only case
where a SQL statement starts with a T_WORD token followed by a left
parenthesis (correct me if I'm mistaken). If that's the case, one
solution would be to just special-case SELECT: if the name of the
"function" is 'select', we treat it as a SQL statement and not a
function call. Of course, this wouldn't apply if the function name is
double-quoted or schema-qualified.

Another technique would be to delay distinguishing between these two
cases until the function is first invoked; then lookup the function name
in pg_proc, and if a candidate function with that name is found, assume
it's a function call. I don't really like this technique, though.

> Also I'm not sure if you can extend this to cope with
> schema-qualified function names.

Sorry, I forgot to mention that -- yes, that is intended.

-Neil

On Thu, 2004-09-16 at 01:19, Andrew Dunstan wrote:
> ISTM that this is being done at the wrong level anyway. I'd like to see 
> a facility available in our SQL, e.g.
> 
>   CALL foo();
> 
> with the restriction that foo() should be declared to return void.

I think these are two distinct issues. The patch I sent along is
intended to make it more natural to invoke functions (and eventually
procedures) from PL/PgSQL, whereas adding support for CALL to SQL is
part of proper support for stored procedures. Gavin and I are hoping to
send a proposal for the latter to -hackers in a few days.

-Neil

Neil Conway <neilc@samurai.com> writes:

> whereas adding support for CALL to SQL is part of proper support for stored
> procedures. Gavin and I are hoping to send a proposal for the latter to
> -hackers in a few days.

What is the point of stored procedures being distinct from functions anyways?
Is there any real difference other than the irregular calling syntax? Is there
anything you can't do with functions that you can do with procedures? Or is it
purely a question of satisfying a spec or providing a more Oracle compatible
syntax?

-- 
greg

On Thu, 16 Sep 2004, Greg Stark wrote:

>
> Neil Conway <neilc@samurai.com> writes:
>
> > whereas adding support for CALL to SQL is part of proper support for stored
> > procedures. Gavin and I are hoping to send a proposal for the latter to
> > -hackers in a few days.
>
> What is the point of stored procedures being distinct from functions anyways?
> Is there any real difference other than the irregular calling syntax? Is there
> anything you can't do with functions that you can do with procedures? Or is it
> purely a question of satisfying a spec or providing a more Oracle compatible
> syntax?

SQL-invoked procedures (ie, stored procedures) differ in two ways from
functions. These are:

1) Procedures do not return a value.

2) Arguments have 'parameter modes'. These modes are: IN - an input
parameter, which has been initialised to some value and is read-only; OUT
- an uninitialised parameter which can be written to; IN OUT - which has
the properties of each of the above.

What this actually means is that you can declare a procedure as follows:

CREATE PROCEDURE foo(IN bar INT, OUT baz INT, OUT bat INT, ...)

That is, a procedure can actually 'return' many values from a call. We can
do this with composite types but, speaking from experience, this can make
migration from PL/SQL just that much harder.

The other thing which SQL-invoked procedures necessitate is support for
the concept of a 'variable'. The reason being that if you use CALL in top
level SQL, you cannot make reference to a field of a relation in any
meaningful way and passing a column reference, for example, as an OUT
parameter does make any sense.


So, SQL2003 defines a few types of variables but the one people may be
most familiar with is the host parameter. This is a named variable which
is referenced as :foo.

I'm putting together a much more detailed email on all this which I hope
to send out in the next few days.

Thanks,

Gavin

Neil Conway <neilc@samurai.com> writes:
> On Thu, 2004-09-16 at 01:19, Andrew Dunstan wrote:
>> ISTM that this is being done at the wrong level anyway.

> I think these are two distinct issues.

I think Andrew has a point: why aren't they the same issue?  It would
certainly be no harder to supportfunc( ... );
as a SQL statement than as something allowed only in plpgsql.  I think
it'd be easier to make it work in the full bison grammar than with some
lookahead hack in plpgsql.
        regards, tom lane

On Fri, 2004-09-17 at 00:34, Tom Lane wrote:
> I think Andrew has a point: why aren't they the same issue?  It would
> certainly be no harder to support
>     func( ... );
> as a SQL statement than as something allowed only in plpgsql.

If there's a consensus that it is better to modify the main grammar so
that unadorned function calls are legal anywhere, that's fine with me.
If anyone doesn't want this, speak up now.

(Note that we need to support CALL proc(...); in SQL for standards
compliance in any event.)

> I think it'd be easier to make it work in the full bison grammar
> than with some lookahead hack in plpgsql.

Well, as it turns out, it's easy to do in PL/PgSQL as well. The SELECT
issue you mentioned doesn't actually pose a problem, because
   SELECT (2, 3, 4);

is _not_ legal SQL in PL/PgSQL (PL/PgSQL requires SELECT INTO). Also, we
get support for double-quotes and schema-qualified function names for
free, because of how the PL/PgSQL scanner works.

-Neil

Neil Conway <neilc@samurai.com> writes:
> On Fri, 2004-09-17 at 00:34, Tom Lane wrote:
>> I think Andrew has a point: why aren't they the same issue?

> (Note that we need to support CALL proc(...); in SQL for standards
> compliance in any event.)

Right.  I'm thinking we could effectively make the CALL keyword optional
(though of course this is just speculation that it can be done without
any parsing conflicts).

> Well, as it turns out, it's easy to do in PL/PgSQL as well. The SELECT
> issue you mentioned doesn't actually pose a problem, because
>     SELECT (2, 3, 4);
> is _not_ legal SQL in PL/PgSQL (PL/PgSQL requires SELECT INTO).

So?  Lookahead won't help you if the INTO is at the end.
        regards, tom lane