Re: SQL-Invoked Procedures for 8.1

Gavin, Neil,

> Following is a proposal to implement what SQL2003 calls 'SQL-Invoked
> Procedures' and what most people refer to as stored procedures. Fujitsu
> will be funding Neil Conway and I to work on this feature.

Which, by the way, is way keen.

My comments are based on having professionally written several hundred
thousand lines of procedural code for PostgreSQL, SQL Server, and Oracle. I
believe that your interpretation of the spec is correct but that there are
several things not covered by the spec, but implemented by other RDBMSes,
which make stored procedures *useful* which have been omitted. I feel
strongly that these things will make a large difference to people thinking of
migrating to PostgreSQL from other DBMSes, and want to make sure that Neil's
implementation does not make them harder, instead of easier, to to implement
later.

> Procedures are nearly identical to functions.

IMHO, this is largely because the spec regards a great deal of SP
functionality to be "implementation-defined", and is thus kept as vague as
possible. In practice, other DBMSes which have both SPs and Functions treat
them *very* differently.

> 3) Procedures can be run in the same savepoint level as the caller when
> OLD SAVEPOINT LEVEL is specified at creation time. According to SQL2003,
> functions must be run on a new savepoint level. From my understanding, we
> do not do this currently.
>
> Work will focus on 1 and 2 until we have the concept of savepoint levels
> with functions. Its possible that we will implement this too if there is
> demand.

One of the things which differentiates SPs on other DBs from PostgreSQL
Functions is transactionality. In SQL Server and Oracle, SPs are not
automatically a transaction; instead, they contain transactions within them.
This is vitally important to DBAs who want to use SPs to automate database
maintenance, loads, transformations, and other activities which require
checkpointing within the course of a program.

For example, I run a nightly data transformation for one client which requires
16 steps with VACUUMs, ANALYZEs and error-handling between them.
Currently, the only way I can implement this for PostgreSQL is to have an
external program (Perl, in my case) manage this and call each step as a
separate function. It would be far easier to manage if I could put all of
the steps, including the vaccums inside one long-running SP, but the required
transaction container prevents this.

> If we go down the route of saying that procedures are a type of function,
> we have the option of allowing users access to OUT and INOUT in functions.
> This would make procedures simply a subset of functions. What do people
> think?

Well, to be frank, my first thought is, why bother? If you're just going to
implement some syntatic sugar on top of the current Function feature, why
bother at all?

Given the opportunity, I would far prefer to set us on a road that would allow
us to replicate -- and exceed -- the functionality of Oracle's PL/SQL. This
proposal does not do that; in fact, if someone were to start implementing
such functionality later they might find this code a stumbling block.

> There will be cases when we need to identify whether a routine is a
> function or a procedure. This could be done two ways. We could say that
> any proc in pg_proc which returns void is a procedure or we could store
> this in some 'protype' column. Thoughts?

Well, see my thoughts above on differentiating SPs from Functions. I
certainly don't think we should be using the same table.

A second point, which I brought up with you on IRC, is to eliminate
overloading and allow named parameter calls on SPs. This is extremely
useful functionality in T-SQL and PL/SQL; in fact, I'd say that it's
essential for any operation that wants to create an SP-centric middleware as
only named parameter calls allow developers to add parameters to existing
procedures without breaking existing calls.

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:
CALL do_some_work( gamma = 'Lord Fontleroy', beta = 7, alpha = 1 )

The idea being that for SPs, schema.name is unique regardless of the
parameters. Even if implementing named parameter calls is beyond the
current spec, I will argue strongly in favor of eliminating overloading for
SPs. Overloading serves no purpose for them and prohibits the whole concept
of default values.

> Other databases, and SQL2003, support a few different implementations what
> could be called variables. In SQL2003, there are a few types of
> 'variables':

I completely follow your thinking about variables, and find it makes sense.

> SET VARIABLE <varname> = <val>
> SHOW VARIABLE <varname>

The problem with this is name collisions with the GUC -- especially as it now
allows add-ins to create their own GUC variables. However intuitive SET and
SHOW are, they will lead to problems. Maybe SETVAR and SHOWVAR? Or using
the PL/pgsql assignment operator for the first, and DISPLAY for the second,
e.g.:

num_logos := 917;
DISPLAY num_logos;

Also, you do realize that this is going to lead to requests for SELECT ....
INTO on the SQL command line, don't you?

> The other option is that we do it at the protocol level and modify libpq
> (and psql) to add support. This would allow us something like:

This may be a better approach. I've personally never been comfortable with
the use of variables outside of SPs and packages; it seems orthagonal to the
declaritive nature of SQL. However, this is a aesthic thing and not really
based on practical considerations.

The more practical consideration is, where will OUT and INOUT parameters be
used? Do we want them returned to the SQL session or directly to the
calling client? I would think that practicality would argue in favor of the
latter; I can't see needing variables in SQL except for testing, and having
them in psql will allow me that.

> That might need some massaging but you get the idea. The commands to psql
> translate to lower level protocol level commands which are: create
> variable, set (ie, modify the value of) variable and describe variable.
> Neil thinks we should have create and set in one step. This is great, since
> it covers most cases, but I'm not sure how we have uninitialised values.
> Perhaps we set to NULL?

Yes.

> The only other question (that I can think of now) with respect to
> variables is how they are affected by transactions. My gut feel is
> that they should have transactional semantics.  For example:

I agree strongly with this, especially since we'll be using Savepoints inside
the SPs. Having one's data mods roll back but not the variable values would
be confusing and lead to a *lot* of debugging.

> We can use the same permissions as for functions.

Agreed.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Thu, Sep 23, 2004 at 11:12:18AM -0700, Josh Berkus wrote:
> A second point, which I brought up with you on IRC, is to eliminate
> overloading and allow named parameter calls on SPs. This is extremely
> useful functionality in T-SQL and PL/SQL; in fact, I'd say that it's
> essential for any operation that wants to create an SP-centric middleware as
> only named parameter calls allow developers to add parameters to existing
> procedures without breaking existing calls.
>
> 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:
> CALL do_some_work( gamma = 'Lord Fontleroy', beta = 7, alpha = 1 )
>
> The idea being that for SPs, schema.name is unique regardless of the
> parameters. Even if implementing named parameter calls is beyond the
> current spec, I will argue strongly in favor of eliminating overloading for
> SPs. Overloading serves no purpose for them and prohibits the whole concept
> of default values.

Since plpgsql seems closer to PL/SQL than TSQL, I'd suggest using the
PL/SQL convention of CALL some_proc( alpha => 'a', bravo => 'b'). Also,
I agree that having defaults is much more useful than overloading when
it comes to creating optional parameters, but I think allowing for
type-overloaded stored procedures is also useful. Or perhaps allowing
for the definiton of a generic input type and a means to tell what
datatype was actually passed in.

> This may be a better approach. I've personally never been comfortable with
> the use of variables outside of SPs and packages; it seems orthagonal to the
> declaritive nature of SQL. However, this is a aesthic thing and not really
> based on practical considerations.

My only comment is I find Oracle's method of having to define a variable
in sql*plus, call your procedure with it, then print the variable, to be
a pain.

One other point I haven't seen brought up: I find Oracle's concept of
packages (and more importantly, private variables, procedures,
functions, etc.) to be extremely useful. It makes it much easier to cut
your code into blocks when you can define internal-only functions and
procedures and not worry about others calling them. It also makes a very
logical way to group code (although schemas in PostgreSQL serve a
similar purpose when it comes to grouping). Likewise, I find PL/SQL's
support of defining a procedure or function within a function to be
useful for grouping code logically. For example:

CREATE OR REPLACE PACKAGE BODY rrd_p AS
PROCEDURE update_rrd_buckets
AS

FUNCTION max_end_time_to_delete (
rrd_id rrd.rrd_id%TYPE
) RETURN TIMESTAMP WITH TIME ZONE
AS
BEGIN
...
END;
BEGIN
...
DELETE FROM table WHERE ts <= max_end_time_to_delete(v_rrd_id);
...
END;
END;

Though, I would prefer if you could define internal procedures/functions
*after* the main code; I think it would greatly improve readability.

I'm not suggesting you try and implement these features now, but you
might want to consider what impact they might have on your design.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

On Fri, 2004-09-24 at 04:12, Josh Berkus wrote:
> My comments are based on having professionally written several hundred
> thousand lines of procedural code for PostgreSQL, SQL Server, and Oracle.

I haven't used stored procedures as implemented elsewhere, so I
appreciate your comments.

> > If we go down the route of saying that procedures are a type of function,
> > we have the option of allowing users access to OUT and INOUT in functions.
> > This would make procedures simply a subset of functions. What do people
> > think?
>
> Well, to be frank, my first thought is, why bother? If you're just going to
> implement some syntatic sugar on top of the current Function feature, why
> bother at all?

As far as possible, I would like to extend the PG concept of "functions"
to offer what people expect from stored procedures, and then implement
syntax sugar so that people can use the standard's stored procedure
syntax.

I think the system is cleaner if we keep the number of distinct concepts
users need to understand to a minimum. That means not making arbitrary
distinctions between stored procedures and functions. It may turn out,
for example, that implementing the kind of transactional behavior people
want for procedures won't be possible within the existing function
infrastructure -- if that's the case, so be it: we'll need to
distinguish procedures and functions. But I'd prefer to unify the
concepts as far as possible.

> Given the opportunity, I would far prefer to set us on a road that would allow
> us to replicate -- and exceed -- the functionality of Oracle's PL/SQL.

That's a priority for me, as well.

> Well, see my thoughts above on differentiating SPs from Functions. I
> certainly don't think we should be using the same table.

Using a different system catalog strikes me as total overkill, and a
surefire way to duplicate a lot of code.

> > SET VARIABLE <varname> = <val>
> > SHOW VARIABLE <varname>
>
> The problem with this is name collisions with the GUC -- especially as it now
> allows add-ins to create their own GUC variables. However intuitive SET and
> SHOW are, they will lead to problems.

I don't see how it will: SET VARIABLE would not share SET's namespace,
so collisions would not be possible.

> > The other option is that we do it at the protocol level and modify libpq
> > (and psql) to add support. [...]
>
> This may be a better approach. I've personally never been comfortable with
> the use of variables outside of SPs and packages; it seems orthagonal to the
> declaritive nature of SQL.

Whether we support protocol-level variables or SQL-level variables has
nothing to do with how those variables can be referenced in queries, so
I'm not sure what you're getting at.

> The more practical consideration is, where will OUT and INOUT parameters be
> used? Do we want them returned to the SQL session or directly to the
> calling client?

I think what you're asking is after a query like:

CALL foo_proc(:some_out_param);

does the client need to explicitly fetch the modified variable, or is it
returned to the client via some means automatically.

Requiring the client to issue a fetch involves an additional roundtrip
(and is an annoyance), so I'm leaning toward returning modified
variables automatically. Perhaps we should allow clients to register
interest in variables -- when the value of that variable changes, they
would receive a protocol message with its new value. I don't see a clean
way to do this without modifying the protocol, though.

(We might have clients register for interest in variables they create by
default.)

> > The only other question (that I can think of now) with respect to
> > variables is how they are affected by transactions. My gut feel is
> > that they should have transactional semantics. [...]
>
> I agree strongly with this, especially since we'll be using Savepoints inside
> the SPs. Having one's data mods roll back but not the variable values would
> be confusing and lead to a *lot* of debugging.

Agreed. BTW, I should note that I'm not taken with the idea of storing
variables in temporary tables -- I don't think it will take too much
work to implement transaction semantics for variables by hand, since
there is no need to worry about concurrency.

-Neil

(I need to mull over your points on overloading and transactions -- I'll
get back to you on that...)

Neil Conway <neilc(at)samurai(dot)com> writes:
> On Fri, 2004-09-24 at 04:12, Josh Berkus wrote:
>> Well, see my thoughts above on differentiating SPs from Functions. I
>> certainly don't think we should be using the same table.

> Using a different system catalog strikes me as total overkill, and a
> surefire way to duplicate a lot of code.

I think that choice will be driven by one thing and one thing only: do
procedures and functions have the same primary key? Which boils down to
whether they have the same semantics about overloaded function names
and resolution of ambiguous parameter types. Personally I think I'd
prefer that they did, but plenty of people have indicated they'd rather
have other features (like defaultable parameters).

regards, tom lane

On Thu, 23 Sep 2004, Josh Berkus wrote:

> One of the things which differentiates SPs on other DBs from PostgreSQL
> Functions is transactionality. In SQL Server and Oracle, SPs are not
> automatically a transaction; instead, they contain transactions within them.
> This is vitally important to DBAs who want to use SPs to automate database
> maintenance, loads, transformations, and other activities which require
> checkpointing within the course of a program.

Good point. Neil and I have been nutting out some of the issues to do with
allowing SPs to start up 'outside' of a transaction. There are some pretty
weird cases like, what if a function calls a procedure? What if that
function is called in the WHERE clause of a query?

ANSI SQL has a flag, MODIFIES SQL DATA, which allows you to differentiate
between SPs which affect the database (and therefore may do something
which needs to be cleaned up in case of error) and those which don't --
ie, they just operate on their arguments.

Still, Neil and I think that allowing people to do their own txn
management inside SPs is important enough to try and look at all the cases
and solve them. We'll detail this later in the week.

> A second point, which I brought up with you on IRC, is to eliminate
> overloading and allow named parameter calls on SPs. This is extremely
> useful functionality in T-SQL and PL/SQL; in fact, I'd say that it's
> essential for any operation that wants to create an SP-centric middleware as
> only named parameter calls allow developers to add parameters to existing
> procedures without breaking existing calls.

We think that not supporting overloading for SPs is reasonable but I am
open to debate. FWIW, it is not supported by Oracle for example.

I'm not sure about named parameter notation (as oracle calls it) for the
arguements. It seems, at least to me, that it would encourage bad
programming but if we want to ease migration it may be worthwhile. Does
anyone know how widely the feature is used?

> The more practical consideration is, where will OUT and INOUT parameters be
> used? Do we want them returned to the SQL session or directly to the
> calling client? I would think that practicality would argue in favor of the
> latter; I can't see needing variables in SQL except for testing, and having
> them in psql will allow me that.

I like the efficiency of returning them after the CALL (after every
query?). But what if someone declares a very large text variable. Do we
need to return it every time?

Thanks for your detailed feed back.

Gavin

On Thu, 23 Sep 2004, Jim C. Nasby wrote:

> > This may be a better approach. I've personally never been comfortable with
> > the use of variables outside of SPs and packages; it seems orthagonal to the
> > declaritive nature of SQL. However, this is a aesthic thing and not really
> > based on practical considerations.
>
> My only comment is I find Oracle's method of having to define a variable
> in sql*plus, call your procedure with it, then print the variable, to be
> a pain.

I agree that it is a bit cumbersome. Any suggestions on how we could
improve on this?

>
> One other point I haven't seen brought up: I find Oracle's concept of
> packages (and more importantly, private variables, procedures,
> functions, etc.) to be extremely useful. It makes it much easier to cut
> your code into blocks when you can define internal-only functions and
> procedures and not worry about others calling them. It also makes a very
> logical way to group code (although schemas in PostgreSQL serve a

I agree that packages give us something like classes in that we can define
related functions/procs into a single namespace. They provide other
features like package level variables and public/private functionality. I
think they major use is namespacing, however, and we can more or less have
that for free with schemas.

> similar purpose when it comes to grouping). Likewise, I find PL/SQL's
> support of defining a procedure or function within a function to be
> useful for grouping code logically. For example:
>
> CREATE OR REPLACE PACKAGE BODY rrd_p AS
> PROCEDURE update_rrd_buckets
> AS
>
> FUNCTION max_end_time_to_delete (
> rrd_id rrd.rrd_id%TYPE
> ) RETURN TIMESTAMP WITH TIME ZONE
> AS
> BEGIN
> ...
> END;

Again, I can see some possibly advantages but I don't think we will see it
in a first generation implementation of procedures :-).

Gavin

Gavin,

> I agree that packages give us something like classes in that we can define
> related functions/procs into a single namespace. They provide other
> features like package level variables and public/private functionality. I
> think they major use is namespacing, however, and we can more or less have
> that for free with schemas.

Don't knock non-namespacing aspects. Now that exception handling inside
functions/procedures will soon be possible, it will become very attractive to
hand off all exception handling in a "package" to a single error-handling
routine. Also, the namespacing itself is non-trivial for financial
applications built on SPs; when you have 1100 SPs, you need an additional
level of namespacing to organize them all ("nested schema" would serve this
as well, but are non-spec).

But, to argue against myself -- some of the aspects of packages are just
re-tracing the history of programming with SQL-script languages. In many
ways, it would make more sense to enhance PL/Perl and PL/Java|J etc. to allow
them to bring to bear their entire apparatus of OO/exception
handling/variables etc, than to re-create a subset of this functionality in
PL/pgSQL. Now that PL/perlNG is underway, I myself am considering migrating
large quantities of PL/pgSQL code to PL/perl.

But even for these "extension" languages, it would be useful to offer a
packaging construct, for organization if for nothing else.

So what am I saying? That we don't want to implement SPs in such a way that
would *prevent* the implementation of packages, but at the same time don't
want to make packages the focus of SPs, at least not yet.

> Good point. Neil and I have been nutting out some of the issues to do with
> allowing SPs to start up 'outside' of a transaction. There are some pretty
> weird cases like, what if a function calls a procedure? What if that
> function is called in the WHERE clause of a query?

Well, there's two possibilites that suggest themselves immediately to me:
1) Allow SPs to call Functions but not vice-versa.
2) For "multi-transactional" SPs, require a flag ("WITH TRANSACTIONS") which
then prevents the SP from being called by any Function.

Despite the limitations it would impose, I actually favor (1). It's far less
complicated than checking for flags at every turn. If we were to do (2),
there's always the possibility of a query calling a function which calls a
single-transaction procedure which calls a multi-transaction procedure, and
who wants to follow up all those chains?

> ANSI SQL has a flag, MODIFIES SQL DATA, which allows you to differentiate
> between SPs which affect the database (and therefore may do something
> which needs to be cleaned up in case of error) and those which don't --
> ie, they just operate on their arguments.

IMHO, this is just another case of the ANSI committee completely failing to
distinguish between SPs and Functions. What would be the point of an SP
that didn't act on the database? Why not just use a function?

From my perspective, the issue of Transactions *is* the fundamental defining
difference between SPs and Functions. The issue of return values and INOUT
parameters are just refinements of this. Functions are "meant" to do
limited processing of data to return a value in the context of a statement;
SPs are "meant" to run independant programs to manipulate the database,
outside of any query.

The fact that I (and many others) often use Functions like SPs is a reflection
of the lack of separate SPs in PostgreSQL and not because I don't think there
should be a distinction.

> I'm not sure about named parameter notation (as oracle calls it) for the
> arguements. It seems, at least to me, that it would encourage bad
> programming but if we want to ease migration it may be worthwhile. Does
> anyone know how widely the feature is used?

I'm not sure how widely it's used with Oracle. It's used very widely with
MSSQL, though.

> I like the efficiency of returning them after the CALL (after every
> query?). But what if someone declares a very large text variable. Do we
> need to return it every time?

I'd say yes. If this is a problem for the user, re-write the SP. Also, that
you said "after every query" shows that you're still thinking of SPs as
identical to Functions. ;-) Presumably, an SP with an OUT param including
8k of text would not be CALLed very often.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Sat, 2 Oct 2004, Josh Berkus wrote:

> Gavin,
>
> > I agree that packages give us something like classes in that we can define
> > related functions/procs into a single namespace. They provide other
> > features like package level variables and public/private functionality. I
> > think they major use is namespacing, however, and we can more or less have
> > that for free with schemas.
>
> Don't knock non-namespacing aspects. Now that exception handling inside

I don't think I was. My point is that since we have an analogous concept,
from a namespacing point of view, we don't need to do the work for 8.1. In
fact, based on a previous submission to get packages in (about 2 years ago
now) by someone working for Zembu (I think), I'd say that packages may be
a lot of work.

> So what am I saying? That we don't want to implement SPs in such a way that
> would *prevent* the implementation of packages, but at the same time don't
> want to make packages the focus of SPs, at least not yet.

If there are any areas of what Neil and I have discussed so far which you
think would hinder a package implementation, please let us know, since
neither of us have much recent experience with them.

Thanks,

Gavin

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

Gavin,

> 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.

Let me advance the reason *I* want them. I do a lot of applications with
extensive, procedure-driven business logic. One of the things I constantly
run up against is when a widely used procedure needs a new parameter. With
functions as they stand now, I have to create a "shell" function that
encompasses the new parameter -- which starts to get hard to track when it's
happened 3 or 4 times. (woe is the lot of those with spec drift)

It also allows code neatness by not forcing you to constantly look up the
order of parameters in the function catalog. For example, this:

sf_cases ( user = 124223,
session = 114643343,
casename = 'VIKTOR',
client = 'KELLEY',
managedby = NULL,
status = 1,
fuzzysearch = TRUE,
filedafter = NULL,
format = 'long',
page = 1,
resultsper = 15 );

Is easier to both read an maintain than:

sf_cases ( 124223, 114643343, 'VIKTOR', 'KELLEY', NULL, 1, TRUE,
NULL, 'long', 1, 15);

Of course, this is as true of functions as it will be of procedures. So half
the functionality that I'm angling for to support with calling named params
could be accomplished within the context of overloading just by extending the
named param patch in 8.0 to cover calling functions/SPs in the format above.

Therefore: the arguments you raise about the difficulty of implementing a
seperate catalog are strong ones, and you are probably correct in the
tradeoff being a bad one.

And, not that I think about it, I have a way to support DEFAULT params within
the context of overloading. Let me muse it over and I'll get back to you.

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

So, do we still need to distinguish if we're not supporting default params?

> 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.

I like this because of the SQL conformance, completely aside from issues of
determinism.

> 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.

Frankly, I agree here. For one thing, any Function being called in that
fashion is effectively being treated as a procedure -- the value it returns
is being thrown away.

--
Josh Berkus
Aglio Database Solutions
San Francisco

On Wed, 6 Oct 2004, Josh Berkus wrote:

[snip]

> Of course, this is as true of functions as it will be of procedures. So half
> the functionality that I'm angling for to support with calling named params
> could be accomplished within the context of overloading just by extending the
> named param patch in 8.0 to cover calling functions/SPs in the format above.

We cannot use named parameter notation with functions due to overloading.
Disregarding the idea of default values, consider:

create function foo(i int, j int) ...
create function foo(j int, i int) ...

If we did:

SELECT foo(j => 1, i => 2)

we would have two candidate functions. So, AFAICT, we cannot used named
parameters with functions. :-(

Gavin

Gavin Sherry wrote:
> We cannot use named parameter notation with functions due to
> overloading. Disregarding the idea of default values, consider:
>
> create function foo(i int, j int) ...
> create function foo(j int, i int) ...

That just means we cannot use the parameter name as a distinguishing
factor in the overloading scheme. Which certainly makes a lot of sense
to me.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> We cannot use named parameter notation with functions due to overloading.
> Disregarding the idea of default values, consider:

> create function foo(i int, j int) ...
> create function foo(j int, i int) ...

> If we did:

> SELECT foo(j => 1, i => 2)

> we would have two candidate functions. So, AFAICT, we cannot used named
> parameters with functions. :-(

It's not really as bad as that. Defaults are killers, but I think that
named params per se are tolerable. Consider that the above pair of
functions would be disallowed anyway because they are both foo(int,int)
--- the param names are not part of the primary key of pg_proc, and I
don't want to see them become so. So a realistic case would be more
like

create function foo(i int, j int) ...
create function foo(j int, i float) ...

SELECT foo(j => 1, i => 2)

and in this case the first foo would be chosen as being an exact match
to the integral input types. (Whether that's reasonable is somewhat
beside the point here; it's how things work in positional parameter
matching, and I'd expect the same in name-based parameter matching.)
Having param names would actually reduce the amount of ambiguity since
you could immediately discard any candidates with a non-matching set
of parameter names.

[ thinks some more... ] Actually I guess the problem comes with

create function foo(i float, j int) ...
create function foo(j int, i float) ...

which is a legal pair of functions from a positional viewpoint, but
would look identical when matching by names. We'd have to think of some
way to forbid that.

The main thing that I'm not happy about is the syntax. I'm going to
resist commandeering => for this purpose, and I don't see any way to use
that symbol for this without forbidding it as a user-defined operator.
I previously suggested using AS, which is already a fully reserved word,
but that suggestion seems not to have garnered any support.

regards, tom lane

Peter Eisentraut wrote:

>Gavin Sherry wrote:
>
>
>>We cannot use named parameter notation with functions due to
>>overloading. Disregarding the idea of default values, consider:
>>
>>create function foo(i int, j int) ...
>>create function foo(j int, i int) ...
>>
>>
>
>That just means we cannot use the parameter name as a distinguishing
>factor in the overloading scheme. Which certainly makes a lot of sense
>to me.
>
>

To me too, It is not at all uncommon to disambiguate on the basis of the
parameter type profile, and ignore for this purpose the formal names.

cheers

andrew

Tom, Gavin, Peter, Andrew,

> [ thinks some more... ] Actually I guess the problem comes with
>
> create function foo(i float, j int) ...
> create function foo(j int, i float) ...
>
> which is a legal pair of functions from a positional viewpoint, but
> would look identical when matching by names. We'd have to think of some
> way to forbid that.

Actually, I don't think we have to forbid it at function/SP creation time.
We already tolerate a certain level of ambiguity thanks to polymorphics.
For example:

primer=# create function ambiguous(anyelement) returns text as ' select
cast($1 as text); ' language sql;
CREATE FUNCTION
primer=# create function ambiguous(anyarray) returns text as ' select
array_to_string($1, '' ''); ' language sql;
CREATE FUNCTION
primer=# select ambiguous(ARRAY[1, 2, 3, 4]);
ERROR: function ambiguous(integer[]) is not unique
HINT: Could not choose a best candidate function. You may need to add
explicit type casts.

I don't see why we can't extend this idea to named parameter calls. If the
user's call is ambiguous, then say so and throw and error. This could even
allow the creation of default params if we just establish a search order:
1) matches same params, same (default) types, same order;
2) matches same params, compatible types, same order;
3) matches same params with compatible types, different order;
4) matches more params if extras are DEFAULT.

Thus, a call of:
CALL sp_ambiguous ( j as 1, k as 5.0 )

Would match:
sp_ambiguous ( j INT, k FLOAT )
before it would match:
sp_ambiguous ( j FLOAT, k INT )
and before it would match:
sp_ambiguous ( k NUMERIC, j INT )
and before it would match:
sp_ambiguous ( k NUMERIC, j BIGINT, m TEXT DEFAULT 'Nothing' );

Obviously, this whole "search pattern" would take time, so it should only
happen when the user makes a named parameter call and, NOT for strictly
ordered parameter calls. Then we'd document that there is a performance
difference between the two.

> The main thing that I'm not happy about is the syntax. I'm going to
> resist commandeering => for this purpose, and I don't see any way to use
> that symbol for this without forbidding it as a user-defined operator.
> I previously suggested using AS, which is already a fully reserved word,
> but that suggestion seems not to have garnered any support.

I don't remember seeing it. I'm perfectly happy with AS; it solves a lot of
problems that = or => would cause.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Gavin Sherry wrote:
> On Wed, 6 Oct 2004, Josh Berkus wrote:
>
> [snip]
>
>
>>Of course, this is as true of functions as it will be of procedures. So half
>>the functionality that I'm angling for to support with calling named params
>>could be accomplished within the context of overloading just by extending the
>>named param patch in 8.0 to cover calling functions/SPs in the format above.
>
>
> We cannot use named parameter notation with functions due to overloading.
> Disregarding the idea of default values, consider:
>
> create function foo(i int, j int) ...
> create function foo(j int, i int) ...

As I see the world ( it could be wrong ) these two functions above have
the same signature, so the second declaration shall be not allowed, do you
want put also the formal parameters names in the function signature ?
Orrible.

Regards
Gaetano Mendola

Tom Lane wrote:

> The main thing that I'm not happy about is the syntax. I'm going to
> resist commandeering => for this purpose, and I don't see any way to use
> that symbol for this without forbidding it as a user-defined operator.
> I previously suggested using AS, which is already a fully reserved word,
> but that suggestion seems not to have garnered any support.

I'm too for use the AS instead of '=>'.

Regards
Gaetano Mendola

On Fri, 8 Oct 2004, Gaetano Mendola wrote:

> Gavin Sherry wrote:
> > On Wed, 6 Oct 2004, Josh Berkus wrote:
> >
> > [snip]
> >
> >
> >>Of course, this is as true of functions as it will be of procedures. So half
> >>the functionality that I'm angling for to support with calling named params
> >>could be accomplished within the context of overloading just by extending the
> >>named param patch in 8.0 to cover calling functions/SPs in the format above.
> >
> >
> > We cannot use named parameter notation with functions due to overloading.
> > Disregarding the idea of default values, consider:
> >
> > create function foo(i int, j int) ...
> > create function foo(j int, i int) ...
>
> As I see the world ( it could be wrong ) these two functions above have
> the same signature, so the second declaration shall be not allowed, do you
> want put also the formal parameters names in the function signature ?
> Orrible.

Oops. Thought-o. I meant:

create function foo(i int, j text) ...
create function foo(j text, i int) ...

Their signature is now:

foo(int, text)
foo(text, int)

Which is legal.

Thanks,

Gavin

On Thu, 7 Oct 2004, Tom Lane wrote:

> Gavin Sherry <swm(at)linuxworld(dot)com(dot)au> writes:
> > We cannot use named parameter notation with functions due to overloading.
> > Disregarding the idea of default values, consider:
>
> > create function foo(i int, j int) ...
> > create function foo(j int, i int) ...
>
> > If we did:
>
> > SELECT foo(j => 1, i => 2)
>
> > we would have two candidate functions. So, AFAICT, we cannot used named
> > parameters with functions. :-(
>
> It's not really as bad as that. Defaults are killers, but I think that
> named params per se are tolerable. Consider that the above pair of
> functions would be disallowed anyway because they are both foo(int,int)
> --- the param names are not part of the primary key of pg_proc, and I
> don't want to see them become so. So a realistic case would be more
> like
>
> create function foo(i int, j int) ...
> create function foo(j int, i float) ...
>
> SELECT foo(j => 1, i => 2)

Yes, I made a thought-o. See my post to Gaetano's email.

>
> and in this case the first foo would be chosen as being an exact match
> to the integral input types. (Whether that's reasonable is somewhat
> beside the point here; it's how things work in positional parameter
> matching, and I'd expect the same in name-based parameter matching.)
> Having param names would actually reduce the amount of ambiguity since
> you could immediately discard any candidates with a non-matching set
> of parameter names.
>
> [ thinks some more... ] Actually I guess the problem comes with
>
> create function foo(i float, j int) ...
> create function foo(j int, i float) ...

This is what I meant to point out -- oops.

>
> which is a legal pair of functions from a positional viewpoint, but
> would look identical when matching by names. We'd have to think of some
> way to forbid that.

Well, we'd error out in the function candidate selection code as it stands
now, I'd say, but we may need to do some work to make the actual error
more user friendly.

>
> The main thing that I'm not happy about is the syntax. I'm going to
> resist commandeering => for this purpose, and I don't see any way to use
> that symbol for this without forbidding it as a user-defined operator.
> I previously suggested using AS, which is already a fully reserved word,
> but that suggestion seems not to have garnered any support.

I agree that => restricts people in a way we are not at the moment. AS
is a better idea but I also like IS, which makes more sense to me. IS is
currently on the func_name_keyword list -- I *think* we could use it.
What do you think?

>
> regards, tom lane
>

Gavin

On Thu, 7 Oct 2004, Peter Eisentraut wrote:

> Gavin Sherry wrote:
> > We cannot use named parameter notation with functions due to
> > overloading. Disregarding the idea of default values, consider:
> >
> > create function foo(i int, j int) ...
> > create function foo(j int, i int) ...
>
> That just means we cannot use the parameter name as a distinguishing
> factor in the overloading scheme. Which certainly makes a lot of sense
> to me.

The above example was a mistake. See other examples in the thread.

Thanks,

Gavin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Gavin Sherry wrote:
| On Fri, 8 Oct 2004, Gaetano Mendola wrote:
|
|
|>Gavin Sherry wrote:
|> > On Wed, 6 Oct 2004, Josh Berkus wrote:
|> >
|> > [snip]
|> >
|> >
|> >>Of course, this is as true of functions as it will be of procedures. So half
|> >>the functionality that I'm angling for to support with calling named params
|> >>could be accomplished within the context of overloading just by extending the
|> >>named param patch in 8.0 to cover calling functions/SPs in the format above.
|> >
|> >
|> > We cannot use named parameter notation with functions due to overloading.
|> > Disregarding the idea of default values, consider:
|> >
|> > create function foo(i int, j int) ...
|> > create function foo(j int, i int) ...
|>
|>As I see the world ( it could be wrong ) these two functions above have
|>the same signature, so the second declaration shall be not allowed, do you
|>want put also the formal parameters names in the function signature ?
|>Orrible.
|
|
| Oops. Thought-o. I meant:
|
| create function foo(i int, j text) ...
| create function foo(j text, i int) ...
|
|
| Their signature is now:
|
| foo(int, text)
| foo(text, int)
|
| Which is legal.

Yes and doing this I think shall be impossible call these two functions with
named parameter, after all as Josh Berkush pointed out foo(anyelement) and
foo(anyarray) have the same problems and when you call foo(ARRAY[2,3]) postgres
complain.

Regards
Gaetano Mendola

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBZdq57UpzwH2SGd4RApriAKDenxbP71nfu9saT5TcGSMTcbgE9wCglWKq
gdYfsWNGzwfAAdjwqA9uvMI=
=0f/k
-----END PGP SIGNATURE-----

On Fri, 8 Oct 2004, Gavin Sherry wrote:

> I agree that => restricts people in a way we are not at the moment. AS
> is a better idea but I also like IS, which makes more sense to me. IS is
> currently on the func_name_keyword list -- I *think* we could use it.
> What do you think?

Josh kindly pointed out the following on IRC:

call some_sp( user IS 19, session IS NULL );

AS now seems quite nice. :-)

Gavin

Gavin,

> I agree that => restricts people in a way we are not at the moment. AS
> is a better idea but I also like IS, which makes more sense to me. IS is
> currently on the func_name_keyword list -- I *think* we could use it.
> What do you think?

I'll give you an example why not:

CALL some_sp ( user IS 19, session IS NULL );

However, Tom, couldn't AS confuse the parser when used to call a named
function in a SELECT clause? Or would named calls be strictly reserved for
SPs and non-statement calls?

Example:

SELECT user, session,
crypt_function ( seed AS 345, content AS pwd_col ) AS munged_pwd
FROM users;

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> I'll give you an example why not:

> CALL some_sp ( user IS 19, session IS NULL );

> However, Tom, couldn't AS confuse the parser when used to call a named
> function in a SELECT clause?

I don't think so. We are talking about

SELECT f(42 AS a, col1 AS b) AS collabel FROM ...

Certainly there's no ambiguity to a person in this: param name AS's are
inside parens, collabel AS's are not. I believe that bison would deal
with this handily ... but I have to admit I've not actually tried to
make the grammar changes for it.

regards, tom lane

On Fri, 8 Oct 2004, Tom Lane wrote:

> Josh Berkus <josh(at)agliodbs(dot)com> writes:
> > I'll give you an example why not:
>
> > CALL some_sp ( user IS 19, session IS NULL );
>
> > However, Tom, couldn't AS confuse the parser when used to call a named
> > function in a SELECT clause?
>
> I don't think so. We are talking about
>
> SELECT f(42 AS a, col1 AS b) AS collabel FROM ...
>
> Certainly there's no ambiguity to a person in this: param name AS's are
> inside parens, collabel AS's are not. I believe that bison would deal
> with this handily ... but I have to admit I've not actually tried to
> make the grammar changes for it.

A quick hack on bison confirms that this doesn't generate any conflicts
(as long as you don't add a_expr AS a_expr to a_expr :-)).

Gavin

[ further response ... ]

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Example:
> SELECT user, session,
> crypt_function ( seed AS 345, content AS pwd_col ) AS munged_pwd
> FROM users;

I failed to look closely at your example before. Apparently you are
thinking of the syntax as being <parameter name> AS <expression>.
I was thinking of <expression> AS <parameter name>, which seems to
me more parallel with the SELECT-list syntax for labeling columns.
I am not sure offhand if one is harder than the other to implement.

regards, tom lane

Tom Lane wrote:
> The main thing that I'm not happy about is the syntax. I'm going to
> resist commandeering => for this purpose, and I don't see any way to
> use that symbol for this without forbidding it as a user-defined
> operator. I previously suggested using AS, which is already a fully
> reserved word, but that suggestion seems not to have garnered any
> support.

As previously mentioned, AS is already used by the SQL standard for a
different purpose in this context.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> As previously mentioned, AS is already used by the SQL standard for a
> different purpose in this context.

Hm? [ reads spec ... ] Oh, you mean <generalized expression>.
Does that actually do anything useful? It looks like it's just
a random shortcut for a CAST expression in a parameter list.

regards, tom lane

Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > As previously mentioned, AS is already used by the SQL standard for
> > a different purpose in this context.
>
> Hm? [ reads spec ... ] Oh, you mean <generalized expression>.
> Does that actually do anything useful? It looks like it's just
> a random shortcut for a CAST expression in a parameter list.

I imagine that since a function call is the only place where the exact
type of a datum really matters and is also ambiguous in general (under
strict SQL rules, of course), they devised an abbreviated casting
syntax for it. In any case, we shouldn't use the syntax for something
else.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Wed, Oct 06, 2004 at 09:17:23PM -0700, Josh Berkus wrote:
> And, not that I think about it, I have a way to support DEFAULT params within
> the context of overloading. Let me muse it over and I'll get back to you.

Yes, but using overloading to implement defaults is a pain. Imagine how
much you need to overload to have 5 default arguments; that equates to 4
stub functions/prodecudes. In the case of adding a single parameter it's
not that bad, but it becomes very onerous if you're trying to provide
default values for a bunch of parameters.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Jim,

> Yes, but using overloading to implement defaults is a pain. Imagine how
> much you need to overload to have 5 default arguments; that equates to 4
> stub functions/prodecudes. In the case of adding a single parameter it's
> not that bad, but it becomes very onerous if you're trying to provide
> default values for a bunch of parameters.

See follow-up discussion regarding ambiguity within overloading schemes.

--
Josh Berkus
Aglio Database Solutions
San Francisco