Thread: SQL-Invoked Procedures for 8.1
Hi all, 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. Procedures ---------- Procedures are nearly identical to functions. From my reading of SQL2003 (see sections 4.27 and 11.50) procedures are different from functions in three ways: 1) Unlike functions, procedures cannot return values. 2) Procedures support parameter modes which specify whether an argument is IN, OUT or IN OUT 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. This makes the difference between procedures and functions quite superficial: procedures are functions which return void and have parameter modes. As such, I think we can largely wrap around the existing function creation code using this rule. That is, CREATE PROCEDURE could and I think should be syntactic sugar. 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? Grammar modifications --------------------- The grammar for creating a procedure, which is a subset of the SQL 2003 grammar, is as follows: CREATE [ OR REPLACE ] PROCEDURE <name> ( [ [ IN | OUT | IN OUT ] [ name ] type, ... ] )<routine characteristics><routinebody> <routine characteristics> are LANGUAGE, null-call and security clauses, all of which are already implemented. <routine body> is the body, same as with the existing functions implementation. DROP PROCEDURE and CALL will also need to be added. System catalog changes ----------------------- 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? To handle parameter modes, we will also need to add an array of "char"s so that we can determine the mode of an element in proargnames[]. Invocation ---------- Since procedures do not return a value they are invoked differently than functions are (at least according to SQL2003). This is because if they were used in a SELECT, UPDATE, DELETE or INSERT clause, their effect would be ambiguous at best. ie, SELECT * FROM foo WHERE bar(); Doesn't make much sense. In SQL2003, procedures are invoked using: CALL foo(); One of our original goals was to allow easier invocation of stored procedures from PL/PgSQL, by allowing "foo()" rather than "CALL foo()" or "PERFORM foo()". Neil submitted some preliminary patches implementing this; following discussion with Tom, it was agreed to make CALL optional at the SQL level, which provides the same effect: http://archives.postgresql.org/pgsql-hackers/2004-09/msg00412.php In this sense, invoking a procedure follows the same rules as invoking a function which returns void. Procedure arguments ------------------- >From what I can tell, there are only 3 ways to pass data to a procedure: 1) As a literal value: CALL foo('Then one day....'); CALL bar(1, 2, 3); CALL baz(current_timestamp) Note that this only works for parameters which are of mode IN (see more below). 2) As a variable Other databases, and SQL2003, support a few different implementations what could be called variables. In SQL2003, there are a few types of 'variables': i) Host parameters ii) Column references to transition variables (OLD and NEW in the case of triggers and rules) iii) Dynamic parameters (ie, ? in the case of prepare) iv) Embedded variables (i) and (iv) are basically bindings to the client side variables. They provide a mechanism for interface designers to allow users to associate variables with a procedure, execute that procedure, and have the new value of the variables available to the language once the execution call returns. JDBC 3.0 focuses fairly heavily on this kind of thing, for example. I think we only need implement one of these and I discuss it below under 'Host Variables'. 3) A transition variable These are the NEW and OLD variables in trigger and rule specifications. If we are calling a procedure, we will need to make sure that every argument is one of these. Only (2) and (3) can be passed as parameters which are marked OUT or INOUT. SQL2003 makes the following distinction between parameter modes: IN - A 'read only' argument. It must be initialised. OUT - An uninitialised argument which, from memory, SQL2003 says *must* be initialised by the function itself. INOUT - A combination of the two above. That is, an initialised argument which can be written to. Host Variables -------------- Host variables are required to make INOUT and OUT parameters useful, so this is an important part of the implementation of procedures. The parser will need to be modified to recognise the host parameter syntax, which is ':<varname>'. We could restrict this to something like CALL or expand it so that normal queries could use variables too. I cannot think of any reason to restrict use but I'm open to what people think. We'll need a host variable node just like we have Const, etc, so that the variable can be propagated through the system. The variable should be substituted within the executor. This ensures that EXPLAIN wont break. Then again, if we don't allow host parameters in normal queries, its not a problem. If we do, I think we could get away with piggy backing on the PREPARE/EXECUTE code with the possibility of allowing the planner to take a look at the current value of a given host variable so that it can generate a better plan. Then again, using variables in standard queries may just be a very bad idea. As for the creation and modification of host parameters, we have two options: an SQL language construct, which MySQL uses for example, or a protocol level mechanism which Oracle appears to use. As far as I can understand from SQL2003, host parameters are predefine there at procedure creation time. I don't really get this and I haven't seen it in another database. If someone could explain it and thinks it would serve us better than that covered below, please let me know :-). If we go down the grammar route, I'd imagine that we do something like: SET VARIABLE <varname> = <val> SHOW VARIABLE <varname> I've used SET and SHOW because they're intuitive. Obviously these are used by GUC and I'm not proposing we use GUC for host parameters as we want a session-local implementation. It would be useful if we could also add some type information to this so make procedure candidate selection easier. Ideas? 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: template1=# \vc x int -- create a variable 'x' of type int template1=# \vs x 2 -- set it to '2' template1=# CALL proc_with_inout_param(:x); CALL template1=# \vp x -- print 'x' 120 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? I think interacting with variables as above is actually quite ugly as well but that has nothing to do with implementation at the protocol level rather my own lack of UI skills :-). So, what does doing this at the protocol level give us? Well, it keeps it out of the grammar and decreases overhead for interfaces. Still, those reasons aren't great when we're look at a protocol modification. I'd love to hear other people's ideas here: maybe I've overlooked a much more attractive alternative. Getting back to the host variables themselves. Most implementations I've seen only support a very limited range of types, such as varchar, numeric and int. I don't see any reason why we can't support the full range of types available in the system. The visibility of variables is restricted to the session which created them. That is, they're just like our implementation of temporary tables. 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: template1=# \vc x int template1=# \vs x 2 template1=# BEGIN; BEGIN template1=# CALL proc_which_sets_arg_to_100(:x); CALL template1=# ABORT; template1=# \vp x What should 'x' be? Following transactional semantics, it should be 2. The only problem is, implementing the transaction semantics might be painful. Bruce made a suggestion to me a while ago that we could just put the variable in a temp table. But that limits the scope for types and it also may slow performance in a long running transaction which modifies a variable a lot. That is, its a lot of overhead. The other option is a hash (or some dynamic structure) in which we attach some transaction information to each variable. We will need to process this from CommitTransaction()/AbortTransaction(). We'll also need to be subtransaction aware. Because we don't need to manage concurrent access to variables, the logic needed to implement transactional semantics by hand shouldn't be too difficult. PL/PgSQL -------- Its in PL/PgSQL that procedures will be most intensively used, I believe. Since variables are already supported in the language we will need to modify that system to make OUT and INOUT parameters work. Currently, we copy each the argument before we call a function so that any (potential) write to it will not be reflected in the caller. We're also protected here by the executor which doesn't allow us to update arguments since they are marked as constants. We'll also need to tweak this. The other thing we will need to look out for if we're executing a procedure is if the user attempts to RETURN a non-void value. This would be an error, of course. Simply using RETURN to return control to the caller shouldn't be a problem in my opinion, however. Neil has also pointed out to me that this is also the exact behaviour required of functions returning void. Permissions ----------- We can use the same permissions as for functions. ---- I will be away for the next few days (until Monday). I hope to have Internet access but I'm not sure. Neil and I have worked through this together so he can also answer any questions too. Gavin
Hi Gavin, Although I have not read the SQL 2003 spec, my recollection of other database products' stored procs differed from your description in one significant way, namely that they could return multiple (and varied) sets of rows. For example, a stored proc could do a SELECT over foo and then a SELECT over bar and return the tuples of both foo and bar. (each having different column counts, types, etc) The JDBC interfaces would appear to illustrate this point. (In CallableStatement) "A CallableStatement can return one ResultSet object or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement." Regards, Grant Gavin Sherry wrote: > Hi all, > > 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. > [lots of interesting detail]
On Thu, 23 Sep 2004, Grant Finnemore wrote: > Hi Gavin, > > Although I have not read the SQL 2003 spec, my recollection of other database > products' stored procs differed from your description in one significant way, > namely that they could return multiple (and varied) sets of rows. > > For example, a stored proc could do a SELECT over foo and then a SELECT over > bar and return the tuples of both foo and bar. (each having different column > counts, types, etc) > > The JDBC interfaces would appear to illustrate this point. > (In CallableStatement) > > "A CallableStatement can return one ResultSet object or multiple ResultSet > objects. Multiple ResultSet objects are handled using operations inherited > from Statement." I read the JDBC 3.0 spec and didn't see this. Like with all specs, some details are hard to find. However, from what I've seen in the spec, I think they have functions in mind here. That being said, I can't think how SQL2003 would allow such behaviour. If you could show us an example, that'd be great. Thanks, Gavin
Quoth the JDBC spec:
public interface CallableStatement
extends PreparedStatement
The interface used to execute SQL stored procedures. The JDBC API provides a
stored procedure SQL escape syntax that allows stored procedures to be called
in a standard way for all RDBMSs. This escape syntax has one form that includes
a result parameter and one that does not. If used, the result parameter must be
registered as an OUT parameter. The other parameters can be used for input,
output or both. Parameters are referred to sequentially, by number, with the
first parameter being 1.
{?= call <procedure-name>[<arg1>,<arg2>, ...]} {call <procedure-name>[<arg1>,<arg2>, ...]}
IN parameter values are set using the set methods inherited from
PreparedStatement. The type of all OUT parameters must be registered prior to
executing the stored procedure; their values are retrieved after execution via
the get methods provided here.
A CallableStatement can return one ResultSet object or multiple ResultSet
objects. Multiple ResultSet objects are handled using operations inherited from
Statement.
For maximum portability, a call's ResultSet objects and update counts should be
processed prior to getting the values of output parameters.
Regards,
Grant
Gavin Sherry wrote:
> On Thu, 23 Sep 2004, Grant Finnemore wrote:
>
>
>>Hi Gavin,
>>
>>Although I have not read the SQL 2003 spec, my recollection of other database
>>products' stored procs differed from your description in one significant way,
>>namely that they could return multiple (and varied) sets of rows.
>>
>>For example, a stored proc could do a SELECT over foo and then a SELECT over
>>bar and return the tuples of both foo and bar. (each having different column
>>counts, types, etc)
>>
>>The JDBC interfaces would appear to illustrate this point.
>>(In CallableStatement)
>>
>>"A CallableStatement can return one ResultSet object or multiple ResultSet
>>objects. Multiple ResultSet objects are handled using operations inherited
>>from Statement."
>
>
> I read the JDBC 3.0 spec and didn't see this. Like with all specs, some
> details are hard to find. However, from what I've seen in the spec, I
> think they have functions in mind here. That being said, I can't think how
> SQL2003 would allow such behaviour. If you could show us an example,
> that'd be great.
>
> Thanks,
>
> Gavin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
On Thu, 23 Sep 2004, Grant Finnemore wrote:
> Quoth the JDBC spec:
>
> public interface CallableStatement
> extends PreparedStatement
>
> The interface used to execute SQL stored procedures. The JDBC API provides a
> stored procedure SQL escape syntax that allows stored procedures to be called
> in a standard way for all RDBMSs. This escape syntax has one form that includes
> a result parameter and one that does not. If used, the result parameter must be
> registered as an OUT parameter. The other parameters can be used for input,
> output or both. Parameters are referred to sequentially, by number, with the
> first parameter being 1.
>
> {?= call <procedure-name>[<arg1>,<arg2>, ...]}
> {call <procedure-name>[<arg1>,<arg2>, ...]}
>
I didn't see this in my copy of the spec, which is version 3.0 FR (final
release). Still, I think we're fine. As I said before, what I think the
spec had in mind was allowing functions to be called from the
callablestatement stuff and have their output put in the first OUT
variable.
This is... reasonable. Compare the stuff Neil's been working on with
"bare" function calls in PL/PgSQL and Tom (I think) saying that it might
be reasonable to just issue func(); as an SQL query: no CALL, no SELECT.
>
> IN parameter values are set using the set methods inherited from
> PreparedStatement. The type of all OUT parameters must be registered prior to
> executing the stored procedure; their values are retrieved after execution via
> the get methods provided here.
>
> A CallableStatement can return one ResultSet object or multiple ResultSet
> objects. Multiple ResultSet objects are handled using operations inherited from
> Statement.
I don't get this multiple ResultSet stuff. All I can think of is that the
spec has this in mind:
CallableStatement cstmt = conn.prepareCall("{call foo(); call bar();}");
or
CallableStatement cstmt = conn.prepareCall("{call foo()} {call bar();}");
or some other permutation.
I see plenty of references to multiple ResultSets but I cannot find an
example or information on how to generate one.
Gavin
> > IN parameter values are set using the set methods inherited from
> > PreparedStatement. The type of all OUT parameters must be
> registered
> > prior to executing the stored procedure; their values are retrieved
> > after execution via the get methods provided here.
> >
> > A CallableStatement can return one ResultSet object or multiple
> > ResultSet objects. Multiple ResultSet objects are handled using
> > operations inherited from Statement.
>
> I don't get this multiple ResultSet stuff. All I can think of
> is that the spec has this in mind:
>
> CallableStatement cstmt = conn.prepareCall("{call foo(); call
> bar();}");
>
> or
>
> CallableStatement cstmt = conn.prepareCall("{call foo()}
> {call bar();}");
>
> or some other permutation.
>
> I see plenty of references to multiple ResultSets but I
> cannot find an example or information on how to generate one.
Not a user of JDBC, but this is fairly common in the ADO/ADO.NET world
with MS SQL Server as well (not sure about other dbs and .NET - I'ev
only used it with mssql and pgsql)... As for an example, something along
the line of (though in my cases usually with a *lot* more parameters):
--
CREATE PROCEDURE get_info_for_user(@userid varchar(16)) ASSELECT something FROM contentstable WHERE userid=@userid
SELECT whatever,somethingelse FROM anothertable WHERE
something=anything
--
You get the point :-)
Then in my .net code I'd do a simple:
SqlDataReader rdr = cmd.ExecuteReader();
... process first result ...
rdr.NextResult();
... process second result...
This is the very simple case. In this case, the only thing you gain is
less server roundtrips and less parsing steps.
In an example of a more complex case, the first part of the stored
procedure will do some complex (and expensive) work to get to a
resulting variable. This variable is then applied to several different
queries after each other, and their respective resultsets are returned
to the client. In this case, you save having to run that complex logic
more than once. (You could cache the result at the client, but if you're
going down that path then you don't need stored procs at all.. It is
usually necessary to keep it in the db to maintain abstraction)
//Magnus
Gavin Sherry wrote:
> I don't get this multiple ResultSet stuff. All I can think of is that the
> spec has this in mind:
>
> CallableStatement cstmt = conn.prepareCall("{call foo(); call bar();}");
>
> or
>
> CallableStatement cstmt = conn.prepareCall("{call foo()} {call bar();}");
>
> or some other permutation.
It's not specific to CallableStatement; you can generate multiple
resultsets from a plain Statement, and CallableStatement is just
inheriting that functionality.
The common way of generating multiple resultsets is, indeed, a
multi-statement query. For example:
> Statement stmt = conn.createStatement();
> stmt.execute("SELECT * FROM foo; SELECT * FROM bar");
>
> ResultSet rs1 = stmt.getResultSet();
> // process rs1
> rs1.close();
>
> boolean moreResults = stmt.getMoreResults();
> assert moreResults;
>
> ResultSet rs2 = stmt.getResultSet();
> // process rs2
> rs2.close();
>
> stmt.close();
AFAIK the multiple-resultset stuff is not *required* functionality in
JDBC, it's just there to support it if it does happen. The postgresql
JDBC driver didn't actually support multiple resultsets at all until
recently.
For function/procedure calls, I'd expect it to look like:
> CallableStatement cstmt = conn.prepareCall("{call foo()}; {call bar()}");
and for the driver to turn that into two separate SELECT/CALL/whatever
queries at the protocol level, and manage the multiple resultsets
itself. The current driver doesn't handle multiple call escapes in one
query at all, but that's really just a limitation of the reasonably dumb
call-escape parser it currently has.
I wouldn't worry about this case unless there's some other reason that a
*single* function/procedure call needs to return more than one set of
results.
> I see plenty of references to multiple ResultSets but I cannot find an
> example or information on how to generate one.
That's because there's no standard way to generate them :)
-O
Hi Magnus,
Yes, this is the situation that I have been thinking about. Specifically
when a single stored procedure returns many recordsets.
Perhaps I should also clarify that the "spec" I have been using is
the JDK javadoc documentation.
Using java with Magnus' procedure: CallableStatement cs = connection.prepareCall("call get_info_for_user ?");
cs.setString(1,"test"); if(cs.execute()) { ResultSet rs = cs.getResultSet(); while(rs != null) { //
Processrs } }
Regards,
Grant
Magnus Hagander wrote:
[snip]
>
> Not a user of JDBC, but this is fairly common in the ADO/ADO.NET world
> with MS SQL Server as well (not sure about other dbs and .NET - I'ev
> only used it with mssql and pgsql)... As for an example, something along
> the line of (though in my cases usually with a *lot* more parameters):
>
> --
> CREATE PROCEDURE get_info_for_user(@userid varchar(16)) AS
> SELECT something FROM contentstable WHERE userid=@userid
>
> SELECT whatever,somethingelse FROM anothertable WHERE
> something=anything
> --
>
> You get the point :-)
> Then in my .net code I'd do a simple:
> SqlDataReader rdr = cmd.ExecuteReader();
> ... process first result ...
> rdr.NextResult();
> ... process second result...
>
On Thu, 23 Sep 2004, Oliver Jowett wrote:
> Gavin Sherry wrote:
>
> > I don't get this multiple ResultSet stuff. All I can think of is that the
> > spec has this in mind:
> >
> > CallableStatement cstmt = conn.prepareCall("{call foo(); call bar();}");
> >
> > or
> >
> > CallableStatement cstmt = conn.prepareCall("{call foo()} {call bar();}");
> >
> > or some other permutation.
>
> It's not specific to CallableStatement; you can generate multiple
> resultsets from a plain Statement, and CallableStatement is just
> inheriting that functionality.
>
> The common way of generating multiple resultsets is, indeed, a
> multi-statement query. For example:
>
> > Statement stmt = conn.createStatement();
> > stmt.execute("SELECT * FROM foo; SELECT * FROM bar");
> >
> > ResultSet rs1 = stmt.getResultSet();
> > // process rs1
> > rs1.close();
> >
> > boolean moreResults = stmt.getMoreResults();
> > assert moreResults;
> >
> > ResultSet rs2 = stmt.getResultSet();
> > // process rs2
> > rs2.close();
> >
> > stmt.close();
>
> AFAIK the multiple-resultset stuff is not *required* functionality in
> JDBC, it's just there to support it if it does happen. The postgresql
> JDBC driver didn't actually support multiple resultsets at all until
> recently.
>
> For function/procedure calls, I'd expect it to look like:
>
> > CallableStatement cstmt = conn.prepareCall("{call foo()}; {call bar()}");
>
> and for the driver to turn that into two separate SELECT/CALL/whatever
> queries at the protocol level, and manage the multiple resultsets
> itself. The current driver doesn't handle multiple call escapes in one
> query at all, but that's really just a limitation of the reasonably dumb
> call-escape parser it currently has.
>
> I wouldn't worry about this case unless there's some other reason that a
> *single* function/procedure call needs to return more than one set of
> results.
>
> > I see plenty of references to multiple ResultSets but I cannot find an
> > example or information on how to generate one.
>
> That's because there's no standard way to generate them :)
Okay. So, its something that can be handled in the driver. That's what I
thought.
Do you have any idea about databases returning result sets from SQL
procedures (ie, not functions).
Thanks,
Gavin
On N, 2004-09-23 at 11:34, Grant Finnemore wrote: > Hi Magnus, > > Yes, this is the situation that I have been thinking about. Specifically > when a single stored procedure returns many recordsets. IIRC support for returning multiple recordsets was removed from postgresql fe-be protocol years ago as "nobody ever needs it" ;) --------------- Hannu
Hannu Krosing wrote: >On N, 2004-09-23 at 11:34, Grant Finnemore wrote: > > >>Hi Magnus, >> >>Yes, this is the situation that I have been thinking about. Specifically >>when a single stored procedure returns many recordsets. >> >> > >IIRC support for returning multiple recordsets was removed from >postgresql fe-be protocol years ago as "nobody ever needs it" ;) > > Until recently I would have said the same thing. A couple of months ago I started a new job where they are MS-SQL based, and they do have several procedures that actually do return multiple recordsets from a single procedure. However this is the only time that I've ever seen any procedure actually require it. Peter
On N, 2004-09-23 at 15:22, Peter Mount wrote: > Hannu Krosing wrote: > > >On N, 2004-09-23 at 11:34, Grant Finnemore wrote: > > > > > >>Hi Magnus, > >> > >>Yes, this is the situation that I have been thinking about. Specifically > >>when a single stored procedure returns many recordsets. > >> > >> > > > >IIRC support for returning multiple recordsets was removed from > >postgresql fe-be protocol years ago as "nobody ever needs it" ;) > > > > > Until recently I would have said the same thing. > > A couple of months ago I started a new job where they are MS-SQL based, > and they do have several procedures that actually do return multiple > recordsets from a single procedure. However this is the only time that > I've ever seen any procedure actually require it. Actually the original support was inhetited from Postgres4.2 where PostQuel had native support for it. It was probably decided that SQL (which replaced PostQuel) would not generate such things. ------------ Hannu
Hannu Krosing wrote: >On N, 2004-09-23 at 15:22, Peter Mount wrote: > > >>Hannu Krosing wrote: >> >> >> >>>On N, 2004-09-23 at 11:34, Grant Finnemore wrote: >>> >>> >>> >>> >>>>Hi Magnus, >>>> >>>>Yes, this is the situation that I have been thinking about. Specifically >>>>when a single stored procedure returns many recordsets. >>>> >>>> >>>> >>>> >>>IIRC support for returning multiple recordsets was removed from >>>postgresql fe-be protocol years ago as "nobody ever needs it" ;) >>> >>> >>> >>> >>Until recently I would have said the same thing. >> >>A couple of months ago I started a new job where they are MS-SQL based, >>and they do have several procedures that actually do return multiple >>recordsets from a single procedure. However this is the only time that >>I've ever seen any procedure actually require it. >> >> > >Actually the original support was inhetited from Postgres4.2 where >PostQuel had native support for it. > >It was probably decided that SQL (which replaced PostQuel) would not >generate such things. > > I'm not so sure as both JDBC & ODBC have implicit support for it and they are younger than SQL. ie: with Prepared/Callable statements in JDBC you are supposed to check for the existence of any other ResultSets when reading the results... this was what I had to do with CallableStatement last week with MSSQL. Peter
Hannu Krosing <hannu@tm.ee> writes:
> IIRC support for returning multiple recordsets was removed from
> postgresql fe-be protocol years ago as "nobody ever needs it" ;)
The protocol can still do it, and so can the backend, but it will
certainly break most if not all clients. Here's an example:
regression=# create table surprise(f1 text);
CREATE TABLE
regression=# create rule r1 as on insert to surprise do
regression-# ( select 'hello' ; select 'how are you' );
CREATE RULE
regression=# insert into surprise values ('boo'); ?column?
-------------how are you
(1 row)
regression=#
The 'hello' result was in fact computed and sent by the backend, but it
was discarded in libpq (see the documentation about PQexec: only the
last resultset returned by a querystring is returned to the caller).
psql could have printed both results, but it would need to use
PQsendQuery/PQgetResult instead of PQexec.
regards, tom lane
Gavin Sherry wrote: > Do you have any idea about databases returning result sets from SQL > procedures (ie, not functions). > As other's have pointed out, this is very common in the MS SQL Server world (and I believe Sysbase also supports it). It works like: <begin proc def> select * from something ... select * from somethingelse ... <end proc def> We get requests for this kind of functionality at least a couple of times a month, and although it's been a few years since I mucked with MSSQL, I found it to be very useful in a number of different circumstances. It is only workable because stored procedures cannot participate in normal SELECT statements. In MSSQL you would do something like: exec sp_my_multiresultset_proc GO -- or -- sp_my_multiresultset_proc GO so the analogy to your stored procedure proposal holds: call sp_my_multiresultset_proc(); -- or -- sp_my_multiresultset_proc(); I had always envisioned implementing this by projecting tuples directly the way that SHOW ALL or EXPLAIN ANALYZE do. See, e.g. ShowAllGUCConfig() in guc.c. Joe
Gavin Sherry <swm@linuxworld.com.au> writes:
> 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.
It's irrelevant since we don't allow functions to call SAVEPOINT/RELEASE/
ROLLBACK TO explicitly, and probably won't do so anytime soon. The only
thing we can really manage for a function is constrained use of
subtransactions a la plpgsql exceptions. This doesn't require the
savepoints to be named at all, so savepoint levels need not enter into it.
> This makes the difference between procedures and functions quite
> superficial: procedures are functions which return void and have parameter
> modes.
If you implement it that way I think it'll be very largely a waste of
effort :-(. What you're talking about seems mere syntactic sugar and
not a fundamental advance in capability.
What I'd like to see is a "procedure" capability which is somehow
outside the transaction system and can therefore invoke BEGIN, COMMIT,
SAVEPOINT, etc. I have no immediate ideas about how to do this, but
I think that's what people are really after when they ask for
server-side procedures. They want to be able, for example, to have
a procedure encapsulate an abort-and-retry loop around a serializable
transaction. (It'd be great if we could do that in a function, but
I haven't thought of a way to make it work.)
I concur with Grant Finnemore's objection as well: people expect
procedures to be able to return resultsets, ie SETOF something,
not only scalar values. Whether this is what SQL2003 says is not
really the issue -- we have to look at what's out there in competing
products.
regards, tom lane
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 12:40:41PM -0400, Tom Lane wrote: > What I'd like to see is a "procedure" capability which is somehow > outside the transaction system and can therefore invoke BEGIN, COMMIT, > SAVEPOINT, etc. I have no immediate ideas about how to do this, but > I think that's what people are really after when they ask for > server-side procedures. They want to be able, for example, to have > a procedure encapsulate an abort-and-retry loop around a serializable > transaction. (It'd be great if we could do that in a function, but > I haven't thought of a way to make it work.) I don't think we can do that in a standard function, at least not without a lot of work. If we think of procedures as different from functions, however, it seems doable. What's needed for this is to isolate the transaction-initiating code from the main query-processing loop. So for CALL statements it wouldn't be invoked, and the procedure would be able to use its own explicit transaction blocks and savepoints. This part is not hard to do at all. It can be handled from the parser, I think. What's harder is handling the execution code. If the procedure uses SPI, we need a mechanism to keep its SPI state, outside the normal transaction-bound SPI stack. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "And as an added bonus, now my computer goes to the toilet for me, leaving me free to spend time on more useful activities! yay slug codefests!" (C. Parker)
Tom Lane wrote: > regression=# create rule r1 as on insert to surprise do > regression-# ( select 'hello' ; select 'how are you' ); > CREATE RULE > The 'hello' result was in fact computed and sent by the backend, but it > was discarded in libpq (see the documentation about PQexec: only the > last resultset returned by a querystring is returned to the caller). > psql could have printed both results, but it would need to use > PQsendQuery/PQgetResult instead of PQexec. Yikes. I thought this couldn't happen under the V3 extended query protocol. The JDBC driver currently sends Describe/Execute and expects exactly one of RowDescription/NoData followed by zero or more DataRows followed by one of CommandComplete/EmptyQueryResponse/PortalSuspended. This seems wrong if there could be multiple resultsets from a single Execute. How can clients distinguish multiple resultsets if they're using the extended query protocol? -O
Oliver Jowett <oliver@opencloud.com> writes:
> The JDBC driver currently sends Describe/Execute and expects exactly one
> of RowDescription/NoData followed by zero or more DataRows followed by
> one of CommandComplete/EmptyQueryResponse/PortalSuspended. This seems
> wrong if there could be multiple resultsets from a single Execute.
This is okay if you know the query was a SELECT, since we don't allow
ON SELECT rules that aren't view-like (ie, DO INSTEAD SELECT something-else).
Non-SELECT queries can return multiple result sets, though, as I
illustrated. It's probably reasonable for the driver to throw these
away if it's not looking for a resultset at all. Or you could follow
PQexec's lead and discard all but the last.
> How can clients distinguish multiple resultsets if they're using the
> extended query protocol?
You'll get multiple repetitions of RowDescription/DataRows.
regards, tom lane
Tom Lane wrote: >>How can clients distinguish multiple resultsets if they're using the >>extended query protocol? > > You'll get multiple repetitions of RowDescription/DataRows. Ah, so the Execute spontaneously generates a RowDescription spontaneously when it hits the second resultset, without needing an extra Describe? -O
Alvaro Herrera <alvherre@dcc.uchile.cl> writes: > What's needed for this is to isolate the transaction-initiating code > from the main query-processing loop. So for CALL statements it wouldn't > be invoked, and the procedure would be able to use its own explicit > transaction blocks and savepoints. Is that really needed? What if the procedure starts in a transaction normally but is just allowed to commit it and start another transaction? I mean it's not like it would be allowed to do any actual work without starting a transaction anyways. -- greg
Oliver Jowett <oliver@opencloud.com> writes:
> Tom Lane wrote:
>>> How can clients distinguish multiple resultsets if they're using the
>>> extended query protocol?
>>
>> You'll get multiple repetitions of RowDescription/DataRows.
> Ah, so the Execute spontaneously generates a RowDescription
> spontaneously when it hits the second resultset, without needing an
> extra Describe?
Oh, wait, you won't get anything. My example was using simple-Query
protocol. In extended Query you get nothing, per this comment in
pquery.c:
/* * If the destination is RemoteExecute, change to None. The reason is * that the client won't be expecting
anytuples, and indeed has no * way to know what they are, since there is no provision for Describe * to send a
RowDescriptionmessage when this portal execution * strategy is in effect. This presently will only affect SELECT
*commands added to non-SELECT queries by rewrite rules: such * commands will be executed, but the results will be
discardedunless * you use "simple Query" protocol. */
We will of course have to rethink this stuff if we want to be able to
send back multiple resultsets from a single procedure call ...
regards, tom lane
Greg Stark <gsstark@mit.edu> writes:
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
>> What's needed for this is to isolate the transaction-initiating code
>> from the main query-processing loop. So for CALL statements it wouldn't
>> be invoked, and the procedure would be able to use its own explicit
>> transaction blocks and savepoints.
> Is that really needed? What if the procedure starts in a transaction normally
> but is just allowed to commit it and start another transaction?
In fact it would more or less have to start in a transaction; keep in
mind that *we cannot do any database access* outside a transaction,
and therefore we could not have looked up the procedure in the system
catalogs in the first place without starting a transaction. We could
however commit that and let the procedure launch its own transactions
(compare to VACUUM, db-wide CLUSTER, etc) once we have read the
procedure body from the catalogs and done any pre-parsing we want to do.
Another point is that we are not really equipped to deal with errors
that occur outside a transaction --- the backend manages not to crash
but it's not really the way things ought to happen. So every action
that the procedure takes will need to be wrapped, explicitly or
implicitly, inside a transaction. This is fairly close to our semantics
for interactive SQL commands, so maybe we could essentially treat the
procedure as a mechanism for pushing commands into the SQL engine.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > In fact it would more or less have to start in a transaction; keep in > mind that *we cannot do any database access* outside a transaction, > and therefore we could not have looked up the procedure in the system > catalogs in the first place without starting a transaction. We could > however commit that and let the procedure launch its own transactions > (compare to VACUUM, db-wide CLUSTER, etc) once we have read the > procedure body from the catalogs and done any pre-parsing we want to do. Well I guess I'm wondering whether there's any need to commit at all. If you do commit then you wouldn't be able to do something like: CREATE PROCEDURE terminate_transaction()COMMIT; END PROCEDURE Admittedly I can't imagine why I would want to do this. But the reference earlier about being able to declare procedures to be in the same savepoint namespace as their caller makes me think this is what the spec has in mind. -- greg
Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> however commit that and let the procedure launch its own transactions
>> (compare to VACUUM, db-wide CLUSTER, etc) once we have read the
>> procedure body from the catalogs and done any pre-parsing we want to do.
> Well I guess I'm wondering whether there's any need to commit at all.
> If you do commit then you wouldn't be able to do something like:
> CREATE PROCEDURE terminate_transaction()
> COMMIT;
> END PROCEDURE
By commit I was thinking of CommitTransactionCommand, which isn't going
to exit a pre-existing transaction block, so I'm not sure that we are
disagreeing.
One interesting point is whether it's possible for one procedure to call
another, and if so what that means for the semantics. Is the inner
procedure allowed to commit a transaction started by the outer one?
regards, tom lane
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
TIMEZONE 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@decibel.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?"
Tom Lane wrote:
> Oliver Jowett <oliver@opencloud.com> writes:
>
>>Tom Lane wrote:
>>
>>>>How can clients distinguish multiple resultsets if they're using the
>>>>extended query protocol?
>>>
>>>You'll get multiple repetitions of RowDescription/DataRows.
>
>
>>Ah, so the Execute spontaneously generates a RowDescription
>>spontaneously when it hits the second resultset, without needing an
>>extra Describe?
>
>
> Oh, wait, you won't get anything. My example was using simple-Query
> protocol. In extended Query you get nothing, per this comment in
> pquery.c:
Ok, thanks. It looks like the driver is doing the right thing then.
Here's a one-line patch that clarifies the Execute protocol docs slightly.
-O
Index: doc/src/sgml/protocol.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/protocol.sgml,v
retrieving revision 1.53
diff -u -c -r1.53 protocol.sgml
*** doc/src/sgml/protocol.sgml 16 Aug 2004 02:12:29 -0000 1.53
--- doc/src/sgml/protocol.sgml 23 Sep 2004 23:02:43 -0000
***************
*** 747,753 ****
The possible
responses to Execute are the same as those described above for queries
issued via simple query protocol, except that Execute doesn't cause
! ReadyForQuery to be issued.
</para>
<para>
--- 747,753 ----
The possible
responses to Execute are the same as those described above for queries
issued via simple query protocol, except that Execute doesn't cause
! ReadyForQuery or RowDescription to be issued.
</para>
<para>
Oliver Jowett <oliver@opencloud.com> writes:
> Here's a one-line patch that clarifies the Execute protocol docs slightly.
Applied.
regards, tom lane
Joe Conway wrote: > Gavin Sherry wrote: > >> Do you have any idea about databases returning result sets from SQL >> procedures (ie, not functions). >> > > As other's have pointed out, this is very common in the MS SQL Server > world (and I believe Sysbase also supports it). It works like: And these databases also return a result status/value from the stored procedure. IIRC this result is limited to an int value. Maarten
Tom Lane wrote: >One interesting point is whether it's possible for one procedure to call >another, and if so what that means for the semantics. Is the inner >procedure allowed to commit a transaction started by the outer one? > > Usually yes a procedure can call another, and it's extremely useful to do so. I'm not so sure about the semantics with transactions, but personally I wouldn't like to see a procedure be able to commit the transaction of it's caller. Peter
Maarten Boekhold wrote: > > Joe Conway wrote: > >> Gavin Sherry wrote: >> >>> Do you have any idea about databases returning result sets from SQL >>> procedures (ie, not functions). >>> >> >> As other's have pointed out, this is very common in the MS SQL Server >> world (and I believe Sysbase also supports it). It works like: > > > And these databases also return a result status/value from the stored > procedure. IIRC this result is limited to an int value. > > Maarten Yes, MS SQL returns an int as far as I know (all the procs I use return an int), but in theory it can be any type. From my useage, the return parameter (if requested) is returned as the first out parameter. Ie, from JDBC, my CallableStateme is of the form: "? = Call dbo.MyProc( ?, ?, ? )" so I can simply use cs.getInt( 1 ); to get at that value. If I don't ask for the return code, then I don't receive it. Peter
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...)
On Fri, 2004-09-24 at 01:56, Joe Conway wrote: > As other's have pointed out, this is very common in the MS SQL Server > world (and I believe Sysbase also supports it). >From looking at the docs, it appears this isn't supported by Oracle or DB2 (correct me if I'm wrong). I can see how it would be useful, but I don't think it needs to be part of the first shot at doing stored procedures. -Neil
On Fri, 2004-09-24 at 05:52, Alvaro Herrera wrote: > I don't think we can do that in a standard function, at least not > without a lot of work. Can you elaborate on why this would be so difficult? -Neil
On Fri, 2004-09-24 at 02:40, Tom Lane wrote: > I concur with Grant Finnemore's objection as well: people expect > procedures to be able to return resultsets, ie SETOF something, > not only scalar values. IMHO most products (and the standard) define stored procedures as not returning _anything_, whether they be scalar values or not. The only counter-example I've seen pointed out is MS SQL. Or are you talking about non-scalar OUT params? -Neil
Neil Conway wrote: > On Fri, 2004-09-24 at 01:56, Joe Conway wrote: > >>As other's have pointed out, this is very common in the MS SQL Server >>world (and I believe Sysbase also supports it). > > >>From looking at the docs, it appears this isn't supported by Oracle or > DB2 (correct me if I'm wrong). I can see how it would be useful, but I > don't think it needs to be part of the first shot at doing stored > procedures. For Oracle you would return refcursors...
Peter Mount <peter@retep.org.uk> writes: > Tom Lane wrote: > > >One interesting point is whether it's possible for one procedure to call > >another, and if so what that means for the semantics. Is the inner > >procedure allowed to commit a transaction started by the outer one? > > Usually yes a procedure can call another, and it's extremely useful to do so. > I'm not so sure about the semantics with transactions, but personally I > wouldn't like to see a procedure be able to commit the transaction of it's > caller. From the quote from the spec referred to earlier it seems the spec anticipates that by default it wouldn't be able to. At least not able to commit refer to savepoints from its caller. Presumably that extends to transactions. However it did provide a way to declare a procedure that could refer to savepoints from its caller. Conceivably that extends to the overall transaction as well. -- greg
Neil Conway <neilc@samurai.com> writes:
> Or are you talking about non-scalar OUT params?
Exactly. I agree that a procedure has no "return value" per se,
but we need to be able to support OUT params that are rowsets.
regards, tom lane
Neil Conway <neilc@samurai.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
Neil Conway wrote: > On Fri, 2004-09-24 at 01:56, Joe Conway wrote: > >>As other's have pointed out, this is very common in the MS SQL Server >>world (and I believe Sysbase also supports it). > >>From looking at the docs, it appears this isn't supported by Oracle or > DB2 (correct me if I'm wrong). I can see how it would be useful, but I > don't think it needs to be part of the first shot at doing stored > procedures. I believe you are correct for Oracle at least. But for people porting over from MSSQL it is a *huge* deal, and given the native windows port of Postgres with 8.0.0, I predict *many* requests for this in upcoming months. Joe
> I believe you are correct for Oracle at least. > > But for people porting over from MSSQL it is a *huge* deal, and given > the native windows port of Postgres with 8.0.0, I predict *many* > requests for this in upcoming months. > Speaking from a commercial perspective. I have had, in the last 60 days over a dozen inquiries of how PostgreSQL 8.0 on WINDOWS compares to MSSQL. The specific question topics have been: 1. Reliability 2. Performance 3. High Availability 4. Features Anything that we can do, within reason to help the migration from MSSQL to PostgreSQL "is a good thing" (tm). Sincerely, Joshua D. Drake > Joe > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if > your > joining column's datatypes do not match -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
On Fri, Sep 24, 2004 at 10:03:33AM -0400, Tom Lane wrote: > Neil Conway <neilc@samurai.com> writes: > > Or are you talking about non-scalar OUT params? > > Exactly. I agree that a procedure has no "return value" per se, > but we need to be able to support OUT params that are rowsets. FWIW, Sybase, MSSQL, and DB2 return recordsets via an 'open' SELECT or OPEN CURSOR statement. IE: you execute a SELECT or an OPEN CURSOR, but don't fetch it into anything. Oracle takes the track of fetching into a refcursor or cursor variable, which you return as an OUT or INOUT parameter. The advantage to MSSQL et all is it's less work/code. The advantage to Oracle is there's no ambiguity. -- Jim C. Nasby, Database Consultant decibel@decibel.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 19:28, Neil Conway wrote: > On Fri, 2004-09-24 at 05:52, Alvaro Herrera wrote: > > I don't think we can do that in a standard function, at least not > > without a lot of work. > > Can you elaborate on why this would be so difficult? I never got a reply to this question -- someone, anyone? (BTW, Gavin and I are working through the suggestions for changes to the stored procedure proposal -- we'll send a revised spec to the list next week. Thanks for the feedback, everyone.) -Neil
On Fri, Sep 24, 2004 at 07:28:09PM +1000, Neil Conway wrote: > On Fri, 2004-09-24 at 05:52, Alvaro Herrera wrote: > > I don't think we can do that in a standard function, at least not > > without a lot of work. > > Can you elaborate on why this would be so difficult? Because you have to keep the function state somewhere. Currently, all functions are handled in the SPI stack, which is dependent on the transaction machinery. So you'd have to move the function state somewhere else -- maybe keeping a special SPI stack outside transaction management. Or maybe the procedure handler does not use SPI at all. In any case, you need some way to handle cleaning it up if the procedure happens to fail; it needs to be able to cope with failing transactions that have to be handled (because some operations in the procedure can be handled), and some others that have to abort the procedure as a whole. This sounds like a meta-transaction (transaction of transactions). Apparently you also need some way to use "savepoints," but since you are not in a normal transaction you can't use the current mechanism for those. (Not sure if you really need cross-transaction "savepoints"). _And_ you need to handle all this from the procedure handler. The current SPI exposes a limited subset of transaction handling to function handlers; you'd need to extend that (unless you are planning to have non-pluggable procedure handlers). -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Tiene valor aquel que admite que es un cobarde" (Fernandel)
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
Gavin Sherry <swm@linuxworld.com.au> writes:
>> I concur with Grant Finnemore's objection as well: people expect
>> procedures to be able to return resultsets, ie SETOF something,
>> not only scalar values. Whether this is what SQL2003 says is not
>> really the issue -- we have to look at what's out there in competing
>> products.
> Agreed. Any suggestions about how a SETOF OUT parameter would be accessed
> by the client? We have a variety of options: returning the results as if
> it was a normal SELECT; returning some kind of delimited string and
> providing an API to scroll it on the client side? There's got to be
> something better than that :-).
For the case of a single OUT SETOF parameter, acting as though the CALL
were a SELECT would work nicely. The hard part is what to do if there
are multiple such parameters. We could possibly return them as
successive SELECT results but this would break a whole lot of pretty
fundamental things at both the protocol and client-library-API level.
(The old protocol actually could handle it, but the V3 protocol is
going to have problems.)
How do you feel about restricting SPs to have at most one SETOF result?
Plan B would be to implement each SETOF result as if it were a cursor.
Say, the system would pass back a cursor (portal) name in the same
way as a scalar OUT result would be returned, and the client would need
to do FETCH operations to pull the actual rows. I'm not sure what to
say about the lifespan of such cursors --- ordinary cursors go away at
transaction end, but if an SP is invoked outside of the transaction
system then this isn't going to do for SP results.
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
Gavin Sherry wrote: > That's fairly bizarre (at least to my view of the world). Say we could > have OUT parameters which were of some SETOF style type I think that would > solve the same problem. That won't satify people moving over from MSSQL/Sybase, but then again, maybe the community at-large doesn't think it is important to satify that group of users. I think this part of the thread actually ties in with the discussion regarding beginning/committing transactions within stored procedures. Think of a stored procedure as a parameterized sql script that is run from within a single statement, rather than as a series of statements piped in from a file. In such a file, you might do begin; INSERT ...; UPDATE ...; commit; SELECT ...; CREATE TEMP TABLE foo AS SELECT ... UPDATE ...; SELECT ...; in order to perform a series of actions while being able to see interim results. In MSSQL, a stored procedure can be (and very often is) used to do something exactly like the above (perhaps related to loading of a data warehouse, or in an interface between two business systems). In fact, T-SQL (the MSSQL/Sybase SQL variant) also supports simple branching, variable assignment, and conditionals, which makes it possible to do some fairly complex processing in stored procs. This is the direction I always hoped Postgres would go with stored procedures. Joe
On Thu, 23 Sep 2004, Joe Conway wrote: > Gavin Sherry wrote: > > Do you have any idea about databases returning result sets from SQL > > procedures (ie, not functions). > > > > As other's have pointed out, this is very common in the MS SQL Server > world (and I believe Sysbase also supports it). It works like: > > <begin proc def> > > select * from something > ... > select * from somethingelse > ... > > <end proc def> > > We get requests for this kind of functionality at least a couple of > times a month, and although it's been a few years since I mucked with > MSSQL, I found it to be very useful in a number of different circumstances. That's fairly bizarre (at least to my view of the world). Say we could have OUT parameters which were of some SETOF style type I think that would solve the same problem. If we wanted to just return the data as if a SELECT had been issued there might be some tricky issues for clients like psql of the row descriptor changed (more rows, new types, etc). On the server side, though, it should be as simple as reinitialising the destination receiver -- although I haven't looked at it that closely yet. Gavin
On Thu, 23 Sep 2004, Tom Lane wrote: > > This makes the difference between procedures and functions quite > > superficial: procedures are functions which return void and have parameter > > modes. > > If you implement it that way I think it'll be very largely a waste of > effort :-(. What you're talking about seems mere syntactic sugar and > not a fundamental advance in capability. > > What I'd like to see is a "procedure" capability which is somehow > outside the transaction system and can therefore invoke BEGIN, COMMIT, > SAVEPOINT, etc. I have no immediate ideas about how to do this, but > I think that's what people are really after when they ask for > server-side procedures. They want to be able, for example, to have > a procedure encapsulate an abort-and-retry loop around a serializable > transaction. (It'd be great if we could do that in a function, but > I haven't thought of a way to make it work.) I made no reference to this in my previous email but I certainly agree that SPs do not give people anything more than a function if they don't have transaction management. I think your idea, mentioned else where, about startup being in its own txn and then calling the SP outside of a txn may work (with some modification to some areas of the code). There are still other cases, like functions calling SPs and SPs calling SPs which potentially provide some messy issues. Neil and I are going to try and work out which cases exist and then see how we can adapt the code or SPs to handle them. > > I concur with Grant Finnemore's objection as well: people expect > procedures to be able to return resultsets, ie SETOF something, > not only scalar values. Whether this is what SQL2003 says is not > really the issue -- we have to look at what's out there in competing > products. Agreed. Any suggestions about how a SETOF OUT parameter would be accessed by the client? We have a variety of options: returning the results as if it was a normal SELECT; returning some kind of delimited string and providing an API to scroll it on the client side? There's got to be something better than that :-). Gavin
On Sat, 2 Oct 2004, Tom Lane wrote: > Gavin Sherry <swm@linuxworld.com.au> writes: > >> I concur with Grant Finnemore's objection as well: people expect > >> procedures to be able to return resultsets, ie SETOF something, > >> not only scalar values. Whether this is what SQL2003 says is not > >> really the issue -- we have to look at what's out there in competing > >> products. > > > Agreed. Any suggestions about how a SETOF OUT parameter would be accessed > > by the client? We have a variety of options: returning the results as if > > it was a normal SELECT; returning some kind of delimited string and > > providing an API to scroll it on the client side? There's got to be > > something better than that :-). > > For the case of a single OUT SETOF parameter, acting as though the CALL > were a SELECT would work nicely. The hard part is what to do if there > are multiple such parameters. We could possibly return them as > successive SELECT results but this would break a whole lot of pretty > fundamental things at both the protocol and client-library-API level. > (The old protocol actually could handle it, but the V3 protocol is > going to have problems.) Yes, that's what I suspected. > > How do you feel about restricting SPs to have at most one SETOF result? I think its a restriction we could do without and others seem to be suggesting that we at least need an analogous feature so that people can have SPs return multiple result sets. > > Plan B would be to implement each SETOF result as if it were a cursor. > Say, the system would pass back a cursor (portal) name in the same > way as a scalar OUT result would be returned, and the client would need > to do FETCH operations to pull the actual rows. I'm not sure what to > say about the lifespan of such cursors --- ordinary cursors go away at > transaction end, but if an SP is invoked outside of the transaction > system then this isn't going to do for SP results. Why not go the whole way and just have a cursor type for these kind of parameters? I'd imagine that this would also allow users an opaque result set. That is, the columns of the result set could be determined at run time. Gavin
On Sat, 2 Oct 2004, Joe Conway wrote: > Gavin Sherry wrote: > > That's fairly bizarre (at least to my view of the world). Say we could > > have OUT parameters which were of some SETOF style type I think that would > > solve the same problem. > > That won't satify people moving over from MSSQL/Sybase, but then again, > maybe the community at-large doesn't think it is important to satify > that group of users. As Tom said in another email, if we want to support a single query generating multiple result sets, we're going to have to break a few things. I'd imagine that if we supported something like a cursor type for OUT parameters, the ODBC driver could be modified to apparently return multiple result sets by scrolling through the cursors. > > I think this part of the thread actually ties in with the discussion > regarding beginning/committing transactions within stored procedures. > Think of a stored procedure as a parameterized sql script that is run > from within a single statement, rather than as a series of statements > piped in from a file. In such a file, you might do > > begin; > INSERT ...; > UPDATE ...; > commit; > SELECT ...; > CREATE TEMP TABLE foo AS SELECT ... > UPDATE ...; > SELECT ...; > > in order to perform a series of actions while being able to see interim > results. In MSSQL, a stored procedure can be (and very often is) used to > do something exactly like the above (perhaps related to loading of a > data warehouse, or in an interface between two business systems). In > fact, T-SQL (the MSSQL/Sybase SQL variant) also supports simple > branching, variable assignment, and conditionals, which makes it > possible to do some fairly complex processing in stored procs. This is > the direction I always hoped Postgres would go with stored procedures. I see the same use for SPs in Postgres. The basic language features, however, would be supported through the use of PL/PgSQL (and other languages). Did you have something else in mind? > > Joe > Gavin
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
While we're discussing things that will possibly mean a different system than the current function language, I have another request: Can we have a means of defining procedures/functions that doesn't involve using quotes? Having to double-quote everything is extremely annoying and prone to errors. I realize that even if procedures/functions aren't defined using quotes there will still be cases where things need to be multi-quoted, but those cases are much rarer. -- Jim C. Nasby, Database Consultant decibel@decibel.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 Mon, Oct 04, 2004 at 01:55:21PM -0500, Jim C. Nasby wrote: > While we're discussing things that will possibly mean a different system > than the current function language, I have another request: Can we have > a means of defining procedures/functions that doesn't involve using > quotes? Having to double-quote everything is extremely annoying and > prone to errors. I realize that even if procedures/functions aren't > defined using quotes there will still be cases where things need to be > multi-quoted, but those cases are much rarer. So you aren't aware of the dollar-quoting feature? You may want to take a look at that ... -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Limítate a mirar... y algun día veras"
Jim C. Nasby wrote: >While we're discussing things that will possibly mean a different system >than the current function language, I have another request: Can we have >a means of defining procedures/functions that doesn't involve using >quotes? Having to double-quote everything is extremely annoying and >prone to errors. I realize that even if procedures/functions aren't >defined using quotes there will still be cases where things need to be >multi-quoted, but those cases are much rarer. > > Have you played with dollar quoting yet? That's in 8.0 for precisely this reason ... The problem with moving entirely from strings would be that we support many languages. If all we had was plpgsql it would be a no-brainer, ISTM. cheers andrew
On Mon, Oct 04, 2004 at 03:10:24PM -0400, Alvaro Herrera wrote: > So you aren't aware of the dollar-quoting feature? You may want to take > a look at that ... Can someone point me to a url? I haven't been able to find anything about this... -- Jim C. Nasby, Database Consultant decibel@decibel.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 Wed, Oct 06, 2004 at 01:34:00AM -0500, Jim C. Nasby wrote: > On Mon, Oct 04, 2004 at 03:10:24PM -0400, Alvaro Herrera wrote: > > So you aren't aware of the dollar-quoting feature? You may want to take > > a look at that ... > > Can someone point me to a url? I haven't been able to find anything > about this... http://developer.postgresql.org/docs/postgres/sql-syntax.html#SQL-SYNTAX-CONSTANTS Keep in mind that this is 8.0 only ... -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) Al principio era UNIX, y UNIX habló y dijo: "Hello world\n". No dijo "Hello New Jersey\n", ni "Hello USA\n".
Alvaro Herrera <alvherre@dcc.uchile.cl> writes: > On Wed, Oct 06, 2004 at 01:34:00AM -0500, Jim C. Nasby wrote: >> On Mon, Oct 04, 2004 at 03:10:24PM -0400, Alvaro Herrera wrote: >>> So you aren't aware of the dollar-quoting feature? You may want to take >>> a look at that ... >> >> Can someone point me to a url? I haven't been able to find anything >> about this... > http://developer.postgresql.org/docs/postgres/sql-syntax.html#SQL-SYNTAX-CONSTANTS Also, many of the function examples in Section V, http://developer.postgresql.org/docs/postgres/server-programming.html have been updated to use dollar-quote style. regards, tom lane
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:
DECLAREi int := 1
BEGINfoo(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@linuxworld.com.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 willbe of procedures. So half>>the functionality that I'm angling for to support with calling named params>>could be accomplishedwithin the context of overloading just by extending the>>named param patch in 8.0 to cover calling functions/SPsin the format above.>>> We cannot use named parameter notation with functions due to overloading.> Disregardingthe 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, 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
On Thu, 7 Oct 2004, Tom Lane wrote: > Gavin Sherry <swm@linuxworld.com.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 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@agliodbs.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
[ further response ... ]
Josh Berkus <josh@agliodbs.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
On Fri, 8 Oct 2004, Tom Lane wrote: > Josh Berkus <josh@agliodbs.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
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@gmx.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@gmx.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/
-----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 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@decibel.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