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
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
-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
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
Peter Eisentraut [EMAIL PROTECTED] 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
Tom Lane wrote:
Peter Eisentraut [EMAIL PROTECTED] 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
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
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
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
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
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
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
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
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
On Thu, 7 Oct 2004, Tom Lane wrote:
Gavin Sherry [EMAIL PROTECTED] 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
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
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 (
Josh Berkus [EMAIL PROTECTED] 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
[ further response ... ]
Josh Berkus [EMAIL PROTECTED] 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
On Fri, 8 Oct 2004, Tom Lane wrote:
Josh Berkus [EMAIL PROTECTED] 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
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
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
Alvaro Herrera [EMAIL PROTECTED] 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
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
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
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.
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
On Sat, 2 Oct 2004, Tom Lane wrote:
Gavin Sherry [EMAIL PROTECTED] 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
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
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
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
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
Gavin Sherry [EMAIL PROTECTED] 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
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
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
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
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
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,
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
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
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:
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
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
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
---(end of broadcast)---
TIP 8: explain
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_,
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
Peter Mount [EMAIL PROTECTED] 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
Neil Conway [EMAIL PROTECTED] 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
---(end of
Neil Conway [EMAIL PROTECTED] 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
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
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
On Fri, Sep 24, 2004 at 10:03:33AM -0400, Tom Lane wrote:
Neil Conway [EMAIL PROTECTED] 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,
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
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
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
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
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 =
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 =
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
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
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
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.
Hannu Krosing [EMAIL PROTECTED] 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:
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
...
Gavin Sherry [EMAIL PROTECTED] 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
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
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
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
Oliver Jowett [EMAIL PROTECTED] 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
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
Alvaro Herrera [EMAIL PROTECTED] 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
Oliver Jowett [EMAIL PROTECTED] 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
Greg Stark [EMAIL PROTECTED] writes:
Alvaro Herrera [EMAIL PROTECTED] 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
Tom Lane [EMAIL PROTECTED] 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
Greg Stark [EMAIL PROTECTED] writes:
Tom Lane [EMAIL PROTECTED] 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
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
Tom Lane wrote:
Oliver Jowett [EMAIL PROTECTED] 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
Oliver Jowett [EMAIL PROTECTED] writes:
Here's a one-line patch that clarifies the Execute protocol docs slightly.
Applied.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL
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
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
80 matches
Mail list logo
