Re: SQL-Invoked Procedures for 8.1

On Thu, 23 Sep 2004, Josh Berkus wrote:

> For anyone who doesn't know what I'm talking about, it's this form:
>
> CREATE PROCEDURE do_some_work ( alpha INT, beta INT, gamma TEXT )
> etc.
>
> Where you can:
> CALL do_some_work( alpha = 5, beta = 7 )
> .. and then gamma picks up its default, if any, or even:

If we make SPs unique by schema.name then we can support default values.
This is largely a feature of SQL Server. The syntax they use is:

<argname> <argtype> = <default value>

That is, something like (in PostgreSQL style syntax)

CREATE PROCEDURE foo(bar int = 1) ...

This syntax is fairly straight forward but another idea, keeping with
syntax else where, is:

CREATE PROCEDURE foo(bar int DEFAULT 1)

Is this too verbose? Do others have thoughts?

My real question, however, is do we want default values at all. Josh has
been fairly keen on them but I haven't seen much outright support for the
idea other than Joe and Joshua (perhaps a few others) putting the argument
that anything which eases the burden of migration from SQL Server is
(potentially) a good thing.

I could see an argument, however, that this (as well as the named
parameter notation) requires us to do a fairly large amount of work for
what is only a potential pay off. That is, to have these features, we
should probably store SPs in a new system catalog since otherwise we'd be
using with two different primary keys and we'd be enforcing different
rules when we add records.

So, the new SP system catalog would have no rettype column but it would
have a parameter modes column and, potentially, a default values column.
Doing this is only a matter of work, but it does leave us with a question
to answer. Can we always distinguish whether or not we're executing a
function or a procedure based on context? The reason is, if we cannot, I
believe, have a procedure with the same name as a function, since this is
the way in which we would determine what it is we need to execute.

I think we can distinguish between functions and procedures based on
context -- there is one case which will affect people, however.

1) Standard routine invocation

In the majority of cases, procedures will be invoked via CALL. We will
have to say that even functions which return void cannot be invoked by
CALL. I don't think that's a loss.

Only functions can be invoked in SELECT, UPDATE, DELETE, INSERT statements
-- which makes sense. So there is no confusion there.

2) Triggers

This is uglier. We currently have a syntax in trigger definition which
reads: ... EXECUTE PROCEDURE <funcname>. I'm not sure what inspired this
but SQL99, 2003, Oracle, DB2 etc allow you to more or less execute SQL --
which may include something which invokes a function or procedure.

I'm not suggesting we go down that path -- unless people really want it --
but it is a case where we cannot distinguish between a function and a
procedure. There are a few ways of tackling this:

i) Only procedures can be execute

Only procedures can be executed by triggers. We may be able to ease the
burden of backward compatibility issues by having pg_dump with 8.1
identify functions which return trigger as being procedures -- but, its
possible that people have defined trigger functions as foo(), foo(int),
etc. That is, they're using overloading, and we wont support that with
procedures -- if we take the path outlined in this email, that is. So,
there are potentially annoying upgrade problems for some users.

We *also* lose some functionality. BEFORE row-based triggers can return
NULL and the executor will be instructured to disregard the current tuple.
We will probably not be able to do this with procedures, unless we want an
OUT parameter to do it. I really dislike this idea.

ii) add EXECUTE FUNCTION

This gives us the option of allowing people to keep the existing
functionality and have a (relatively) simple upgrade path. It does,
however, move us further away from the spec and what other databases have.

iii) Support functions and procedures through SQL

Instead of adding EXECUTE FUNCTION, we could have:

FOR EACH { ROW | STATEMENT } { SELECT <funcname> | CALL <procedure> }

This gives us the option, I believe, of moving to full SQL comformance in
the future as well as giving people (and pg_dump) and upgrade path.

iv) Any other ideas?

3) PL/PgSQL

Neil's recent "bare" function calls patch for PL/PgSQL creates a situation
where we cannot distinguish between functions and procedures. For example:

DECLARE
i int := 1
BEGIN
foo(i);
END:

Is foo() a function or procedure? I think it is reasonable to say that
only procedures can be called in this fashion, and that function need be
invoked with PERFORM or in a query, as we have in 7.4, etc.

There are probably other cases that I haven't thought of.

Ideas, comments, criticisms?

Thanks,

Gavin