Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 [EMAIL PROTECTED] 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? ---(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
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(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
Re: [HACKERS] SQL-Invoked Procedures for 8.1
-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- ---(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
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 for a CAST expression in a parameter list. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SQL-Invoked Procedures for 8.1
Gavin Sherry wrote: On Wed, 6 Oct 2004, Josh Berkus wrote: [snip] Of course, this is as true of functions as it will be of procedures. So half the functionality that I'm angling for to support with calling named params could be accomplished within the context of overloading just by extending the named param patch in 8.0 to cover calling functions/SPs in the format above. We cannot use named parameter notation with functions due to overloading. Disregarding the idea of default values, consider: create function foo(i int, j int) ... create function foo(j int, i int) ... As I see the world ( it could be wrong ) these two functions above have the same signature, so the second declaration shall be not allowed, do you want put also the formal parameters names in the function signature ? Orrible. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(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
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL-Invoked Procedures for 8.1
[ 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 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SQL-Invoked Procedures for 8.1
On Thu, 23 Sep 2004, Josh Berkus wrote:
For anyone who doesn't know what I'm talking about, it's this form:
CREATE PROCEDURE do_some_work ( alpha INT, beta INT, gamma TEXT )
etc.
Where you can:
CALL do_some_work( alpha = 5, beta = 7 )
.. and then gamma picks up its default, if any, or even:
If we make SPs unique by schema.name then we can support default values.
This is largely a feature of SQL Server. The syntax they use is:
argname argtype = default value
That is, something like (in PostgreSQL style syntax)
CREATE PROCEDURE foo(bar int = 1) ...
This syntax is fairly straight forward but another idea, keeping with
syntax else where, is:
CREATE PROCEDURE foo(bar int DEFAULT 1)
Is this too verbose? Do others have thoughts?
My real question, however, is do we want default values at all. Josh has
been fairly keen on them but I haven't seen much outright support for the
idea other than Joe and Joshua (perhaps a few others) putting the argument
that anything which eases the burden of migration from SQL Server is
(potentially) a good thing.
I could see an argument, however, that this (as well as the named
parameter notation) requires us to do a fairly large amount of work for
what is only a potential pay off. That is, to have these features, we
should probably store SPs in a new system catalog since otherwise we'd be
using with two different primary keys and we'd be enforcing different
rules when we add records.
So, the new SP system catalog would have no rettype column but it would
have a parameter modes column and, potentially, a default values column.
Doing this is only a matter of work, but it does leave us with a question
to answer. Can we always distinguish whether or not we're executing a
function or a procedure based on context? The reason is, if we cannot, I
believe, have a procedure with the same name as a function, since this is
the way in which we would determine what it is we need to execute.
I think we can distinguish between functions and procedures based on
context -- there is one case which will affect people, however.
1) Standard routine invocation
In the majority of cases, procedures will be invoked via CALL. We will
have to say that even functions which return void cannot be invoked by
CALL. I don't think that's a loss.
Only functions can be invoked in SELECT, UPDATE, DELETE, INSERT statements
-- which makes sense. So there is no confusion there.
2) Triggers
This is uglier. We currently have a syntax in trigger definition which
reads: ... EXECUTE PROCEDURE funcname. I'm not sure what inspired this
but SQL99, 2003, Oracle, DB2 etc allow you to more or less execute SQL --
which may include something which invokes a function or procedure.
I'm not suggesting we go down that path -- unless people really want it --
but it is a case where we cannot distinguish between a function and a
procedure. There are a few ways of tackling this:
i) Only procedures can be execute
Only procedures can be executed by triggers. We may be able to ease the
burden of backward compatibility issues by having pg_dump with 8.1
identify functions which return trigger as being procedures -- but, its
possible that people have defined trigger functions as foo(), foo(int),
etc. That is, they're using overloading, and we wont support that with
procedures -- if we take the path outlined in this email, that is. So,
there are potentially annoying upgrade problems for some users.
We *also* lose some functionality. BEFORE row-based triggers can return
NULL and the executor will be instructured to disregard the current tuple.
We will probably not be able to do this with procedures, unless we want an
OUT parameter to do it. I really dislike this idea.
ii) add EXECUTE FUNCTION
This gives us the option of allowing people to keep the existing
functionality and have a (relatively) simple upgrade path. It does,
however, move us further away from the spec and what other databases have.
iii) Support functions and procedures through SQL
Instead of adding EXECUTE FUNCTION, we could have:
FOR EACH { ROW | STATEMENT } { SELECT funcname | CALL procedure }
This gives us the option, I believe, of moving to full SQL comformance in
the future as well as giving people (and pg_dump) and upgrade path.
iv) Any other ideas?
3) PL/PgSQL
Neil's recent bare function calls patch for PL/PgSQL creates a situation
where we cannot distinguish between functions and procedures. For example:
DECLARE
i int := 1
BEGIN
foo(i);
END:
Is foo() a function or procedure? I think it is reasonable to say that
only procedures can be called in this fashion, and that function need be
invoked with PERFORM or in a query, as we have in 7.4, etc.
There are probably other cases that I haven't thought of.
Ideas, comments, criticisms?
Thanks,
Gavin
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 -- 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(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
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(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
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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...) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 analyze is your friend
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(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
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL ---(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
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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, 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 [EMAIL PROTECTED] 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? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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
---(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
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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)) AS
SELECT something FROM contentstable WHERE [EMAIL PROTECTED]
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
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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) {
// Process rs
}
}
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 [EMAIL PROTECTED]
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...
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(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
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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:
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
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL-Invoked Procedures for 8.1
Gavin, Neil, Following is a proposal to implement what SQL2003 calls 'SQL-Invoked Procedures' and what most people refer to as stored procedures. Fujitsu will be funding Neil Conway and I to work on this feature. Which, by the way, is way keen. My comments are based on having professionally written several hundred thousand lines of procedural code for PostgreSQL, SQL Server, and Oracle. I believe that your interpretation of the spec is correct but that there are several things not covered by the spec, but implemented by other RDBMSes, which make stored procedures *useful* which have been omitted. I feel strongly that these things will make a large difference to people thinking of migrating to PostgreSQL from other DBMSes, and want to make sure that Neil's implementation does not make them harder, instead of easier, to to implement later. Procedures are nearly identical to functions. IMHO, this is largely because the spec regards a great deal of SP functionality to be implementation-defined, and is thus kept as vague as possible. In practice, other DBMSes which have both SPs and Functions treat them *very* differently. 3) Procedures can be run in the same savepoint level as the caller when OLD SAVEPOINT LEVEL is specified at creation time. According to SQL2003, functions must be run on a new savepoint level. From my understanding, we do not do this currently. Work will focus on 1 and 2 until we have the concept of savepoint levels with functions. Its possible that we will implement this too if there is demand. One of the things which differentiates SPs on other DBs from PostgreSQL Functions is transactionality.In SQL Server and Oracle, SPs are not automatically a transaction; instead, they contain transactions within them. This is vitally important to DBAs who want to use SPs to automate database maintenance, loads, transformations, and other activities which require checkpointing within the course of a program. For example, I run a nightly data transformation for one client which requires 16 steps with VACUUMs, ANALYZEs and error-handling between them. Currently, the only way I can implement this for PostgreSQL is to have an external program (Perl, in my case) manage this and call each step as a separate function.It would be far easier to manage if I could put all of the steps, including the vaccums inside one long-running SP, but the required transaction container prevents this. If we go down the route of saying that procedures are a type of function, we have the option of allowing users access to OUT and INOUT in functions. This would make procedures simply a subset of functions. What do people think? Well, to be frank, my first thought is, why bother? If you're just going to implement some syntatic sugar on top of the current Function feature, why bother at all? Given the opportunity, I would far prefer to set us on a road that would allow us to replicate -- and exceed -- the functionality of Oracle's PL/SQL. This proposal does not do that; in fact, if someone were to start implementing such functionality later they might find this code a stumbling block. There will be cases when we need to identify whether a routine is a function or a procedure. This could be done two ways. We could say that any proc in pg_proc which returns void is a procedure or we could store this in some 'protype' column. Thoughts? Well, see my thoughts above on differentiating SPs from Functions.I certainly don't think we should be using the same table. A second point, which I brought up with you on IRC, is to eliminate overloading and allow named parameter calls on SPs. This is extremely useful functionality in T-SQL and PL/SQL; in fact, I'd say that it's essential for any operation that wants to create an SP-centric middleware as only named parameter calls allow developers to add parameters to existing procedures without breaking existing calls. For anyone who doesn't know what I'm talking about, it's this form: CREATE PROCEDURE do_some_work ( alpha INT, beta INT, gamma TEXT ) etc. Where you can: CALL do_some_work( alpha = 5, beta = 7 ) .. and then gamma picks up its default, if any, or even: CALL do_some_work( gamma = 'Lord Fontleroy', beta = 7, alpha = 1 ) The idea being that for SPs, schema.name is unique regardless of the parameters.Even if implementing named parameter calls is beyond the current spec, I will argue strongly in favor of eliminating overloading for SPs. Overloading serves no purpose for them and prohibits the whole concept of default values. Other databases, and SQL2003, support a few different implementations what could be called variables. In SQL2003, there are a few types of 'variables': I completely follow your thinking about variables, and find it makes sense. SET VARIABLE varname = val SHOW VARIABLE varname The problem with this is name collisions with the GUC -- especially as it
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 any tuples, and indeed has no * way to know what they are, since there is no provision for Describe * to send a RowDescription message 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 discarded unless * 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SQL-Invoked Procedures for 8.1
On Thu, Sep 23, 2004 at 11:12:18AM -0700, Josh Berkus wrote: A second point, which I brought up with you on IRC, is to eliminate overloading and allow named parameter calls on SPs. This is extremely useful functionality in T-SQL and PL/SQL; in fact, I'd say that it's essential for any operation that wants to create an SP-centric middleware as only named parameter calls allow developers to add parameters to existing procedures without breaking existing calls. For anyone who doesn't know what I'm talking about, it's this form: CREATE PROCEDURE do_some_work ( alpha INT, beta INT, gamma TEXT ) etc. Where you can: CALL do_some_work( alpha = 5, beta = 7 ) .. and then gamma picks up its default, if any, or even: CALL do_some_work( gamma = 'Lord Fontleroy', beta = 7, alpha = 1 ) The idea being that for SPs, schema.name is unique regardless of the parameters.Even if implementing named parameter calls is beyond the current spec, I will argue strongly in favor of eliminating overloading for SPs. Overloading serves no purpose for them and prohibits the whole concept of default values. Since plpgsql seems closer to PL/SQL than TSQL, I'd suggest using the PL/SQL convention of CALL some_proc( alpha = 'a', bravo = 'b'). Also, I agree that having defaults is much more useful than overloading when it comes to creating optional parameters, but I think allowing for type-overloaded stored procedures is also useful. Or perhaps allowing for the definiton of a generic input type and a means to tell what datatype was actually passed in. This may be a better approach. I've personally never been comfortable with the use of variables outside of SPs and packages; it seems orthagonal to the declaritive nature of SQL. However, this is a aesthic thing and not really based on practical considerations. My only comment is I find Oracle's method of having to define a variable in sql*plus, call your procedure with it, then print the variable, to be a pain. One other point I haven't seen brought up: I find Oracle's concept of packages (and more importantly, private variables, procedures, functions, etc.) to be extremely useful. It makes it much easier to cut your code into blocks when you can define internal-only functions and procedures and not worry about others calling them. It also makes a very logical way to group code (although schemas in PostgreSQL serve a similar purpose when it comes to grouping). Likewise, I find PL/SQL's support of defining a procedure or function within a function to be useful for grouping code logically. For example: CREATE OR REPLACE PACKAGE BODY rrd_p AS PROCEDURE update_rrd_buckets AS FUNCTION max_end_time_to_delete ( rrd_id rrd.rrd_id%TYPE ) RETURN TIMESTAMP WITH TIME ZONE AS BEGIN ... END; BEGIN ... DELETE FROM table WHERE ts = max_end_time_to_delete(v_rrd_id); ... END; END; Though, I would prefer if you could define internal procedures/functions *after* the main code; I think it would greatly improve readability. I'm not suggesting you try and implement these features now, but you might want to consider what impact they might have on your design. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] 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? ---(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
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 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 - 1.53 --- doc/src/sgml/protocol.sgml 23 Sep 2004 23:02:43 - *** *** 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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 PROTECTED]
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] SQL-Invoked Procedures for 8.1
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
