Re: SQL-Invoked Procedures for 8.1
От | Gavin Sherry |
---|---|
Тема | Re: SQL-Invoked Procedures for 8.1 |
Дата | |
Msg-id | Pine.LNX.4.58.0410070959280.9764@linuxworld.com.au обсуждение исходный текст |
Ответ на | Re: SQL-Invoked Procedures for 8.1 (Josh Berkus <josh@agliodbs.com>) |
Ответы |
Re: SQL-Invoked Procedures for 8.1
(Josh Berkus <josh@agliodbs.com>)
|
Список | pgsql-hackers |
On Thu, 23 Sep 2004, Josh Berkus wrote: > For anyone who doesn't know what I'm talking about, it's this form: > > CREATE PROCEDURE do_some_work ( alpha INT, beta INT, gamma TEXT ) > etc. > > Where you can: > CALL do_some_work( alpha = 5, beta = 7 ) > .. and then gamma picks up its default, if any, or even: If we make SPs unique by schema.name then we can support default values. This is largely a feature of SQL Server. The syntax they use is: <argname> <argtype> = <default value> That is, something like (in PostgreSQL style syntax) CREATE PROCEDURE foo(bar int = 1) ... This syntax is fairly straight forward but another idea, keeping with syntax else where, is: CREATE PROCEDURE foo(bar int DEFAULT 1) Is this too verbose? Do others have thoughts? My real question, however, is do we want default values at all. Josh has been fairly keen on them but I haven't seen much outright support for the idea other than Joe and Joshua (perhaps a few others) putting the argument that anything which eases the burden of migration from SQL Server is (potentially) a good thing. I could see an argument, however, that this (as well as the named parameter notation) requires us to do a fairly large amount of work for what is only a potential pay off. That is, to have these features, we should probably store SPs in a new system catalog since otherwise we'd be using with two different primary keys and we'd be enforcing different rules when we add records. So, the new SP system catalog would have no rettype column but it would have a parameter modes column and, potentially, a default values column. Doing this is only a matter of work, but it does leave us with a question to answer. Can we always distinguish whether or not we're executing a function or a procedure based on context? The reason is, if we cannot, I believe, have a procedure with the same name as a function, since this is the way in which we would determine what it is we need to execute. I think we can distinguish between functions and procedures based on context -- there is one case which will affect people, however. 1) Standard routine invocation In the majority of cases, procedures will be invoked via CALL. We will have to say that even functions which return void cannot be invoked by CALL. I don't think that's a loss. Only functions can be invoked in SELECT, UPDATE, DELETE, INSERT statements -- which makes sense. So there is no confusion there. 2) Triggers This is uglier. We currently have a syntax in trigger definition which reads: ... EXECUTE PROCEDURE <funcname>. I'm not sure what inspired this but SQL99, 2003, Oracle, DB2 etc allow you to more or less execute SQL -- which may include something which invokes a function or procedure. I'm not suggesting we go down that path -- unless people really want it -- but it is a case where we cannot distinguish between a function and a procedure. There are a few ways of tackling this: i) Only procedures can be execute Only procedures can be executed by triggers. We may be able to ease the burden of backward compatibility issues by having pg_dump with 8.1 identify functions which return trigger as being procedures -- but, its possible that people have defined trigger functions as foo(), foo(int), etc. That is, they're using overloading, and we wont support that with procedures -- if we take the path outlined in this email, that is. So, there are potentially annoying upgrade problems for some users. We *also* lose some functionality. BEFORE row-based triggers can return NULL and the executor will be instructured to disregard the current tuple. We will probably not be able to do this with procedures, unless we want an OUT parameter to do it. I really dislike this idea. ii) add EXECUTE FUNCTION This gives us the option of allowing people to keep the existing functionality and have a (relatively) simple upgrade path. It does, however, move us further away from the spec and what other databases have. iii) Support functions and procedures through SQL Instead of adding EXECUTE FUNCTION, we could have: FOR EACH { ROW | STATEMENT } { SELECT <funcname> | CALL <procedure> } This gives us the option, I believe, of moving to full SQL comformance in the future as well as giving people (and pg_dump) and upgrade path. iv) Any other ideas? 3) PL/PgSQL Neil's recent "bare" function calls patch for PL/PgSQL creates a situation where we cannot distinguish between functions and procedures. For example: DECLAREi int := 1 BEGINfoo(i); END: Is foo() a function or procedure? I think it is reasonable to say that only procedures can be called in this fashion, and that function need be invoked with PERFORM or in a query, as we have in 7.4, etc. There are probably other cases that I haven't thought of. Ideas, comments, criticisms? Thanks, Gavin
В списке pgsql-hackers по дате отправления: