Thread: Proposal: real procedures again (8.4)
Hello, I found lot of discus about this topic. http://www.postgresql.org/docs/faqs.TODO.html http://archives.postgresql.org/pgsql-hackers/2003-08/msg00501.php http://archives.postgresql.org/pgsql-hackers/2004-09/msg00734.php http://archives.postgresql.org/pgsql-hackers/2004-08/msg00872.php http://archives.postgresql.org/pgsql-hackers/2004-09/msg00702.php There is one result - OUT params for functions. I propose start with simple goals that we can enhance in future. First goal: Procedures support for better conformance with ANSI SQL: * procedure returns any only through OUT, INOUT params, * procedure has own executor, that allows byref params (and own transaction management in future), * procedure can be overloaded, * procedure can not returns recordset or multi recordset, * procedure doesn't support default parameters, * SQL statement CALL allows only expression (this proposal doesn't need session variables) for older environments * new SPI_exec_procedures API (allows binding to host variables) and some similar in libpq, that allow CALL implementation in pgsql and others. * new internal exec_exec_proc (allow ref on datum variable) used in plpgsql statement CALL. * new V2 calling convention (maybe based on SQL/CLI) * no changes in current functions support Later: * procedure can manages transactions, * procedure can returns recordset or multi recordset, * procedure allows default parameters, * CALL statement allows session variables * no changes in current functions support Why new calling convention? I would to support byref variables and then I have to carry memory context info ... and maybe some others Nice a weekend Pavel Stehule p.s. Why procedures? New parts of ANSI SQL use it, and what is worse, they use methods: http://www.wiscorp.com/H2-2005-350-WG4-wlg005-Part-7-History-2nd-Edition-Text-for-Working-Draft.pdf
"Pavel Stehule" <pavel.stehule@gmail.com> writes: > Later: > * procedure can manages transactions, Personally this is the only actual feature on the list that seems to have any point to me. > Why new calling convention? I would to support byref variables and > then I have to carry memory context info ... and maybe some others I think first you have to invent something for the by-ref parameter to refer to. We don't currently have any kind of "variables" which can be mutated. We just have immutable datums which get passed up the query. Effectively SQL is a functional programming language in that sense. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark <stark@enterprisedb.com> writes:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>> Why new calling convention? I would to support byref variables and
>> then I have to carry memory context info ... and maybe some others
> I think first you have to invent something for the by-ref parameter to refer
> to.
Most of that sounded to me like a proposal to re-invent ecpg. If there
were such a large demand for doing things that way, there would be many
more users of ecpg than bare libpq. AFAICT, though, *very* few people
use ecpg.
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > Gregory Stark <stark@enterprisedb.com> writes: >> "Pavel Stehule" <pavel.stehule@gmail.com> writes: >>> Why new calling convention? I would to support byref variables and >>> then I have to carry memory context info ... and maybe some others > >> I think first you have to invent something for the by-ref parameter to refer >> to. > > Most of that sounded to me like a proposal to re-invent ecpg. If there > were such a large demand for doing things that way, there would be many > more users of ecpg than bare libpq. AFAICT, though, *very* few people > use ecpg. ecpg is a client-side thing though, isn't it? So, for example if you are writing some large batch job for which you want to process many records, occasionally updating some of them you would end up having to download all the data to the client. I think what people want is something like plpgsql, ie, it runs on the server and can access the data without having to marshal and unmarshal it over the wire to a client. They just want to be able to use plpgsql outside of a transaction so they can start and commit transactions within the execution context of the PL execution environment. And they want to program it using a procedural style, with mutable per-session variables storing state. It's not clear to me whether those variables should be transactional but I don't think most people expect them to be. They expect cheap per-session non-transactional variables which have no additional overhead over plpgsql variables but can be referenced easily from any SQL so if the variable's value is change the meaning of their SQL magically changes. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
2007/10/27, Tom Lane <tgl@sss.pgh.pa.us>: > Gregory Stark <stark@enterprisedb.com> writes: > > "Pavel Stehule" <pavel.stehule@gmail.com> writes: > >> Why new calling convention? I would to support byref variables and > >> then I have to carry memory context info ... and maybe some others > > > I think first you have to invent something for the by-ref parameter to refer > > to. > > Most of that sounded to me like a proposal to re-invent ecpg. If there > were such a large demand for doing things that way, there would be many > more users of ecpg than bare libpq. AFAICT, though, *very* few people > use ecpg. > With procedures we can be in conformance with ANSI standard and others databases. New SQL2006 standards contains lot of SQL/PSM code and will be usefull, if we can port this code without changes. New calling convention can simplify life. It can support more than one output value. Actual solution is practical, but is too complicated for C code, because I have to do create tuple when I would to return two ints ... Pavel
"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> 2007/10/27, Tom Lane <tgl@sss.pgh.pa.us>:
>> Most of that sounded to me like a proposal to re-invent ecpg. If there
>> were such a large demand for doing things that way, there would be many
>> more users of ecpg than bare libpq. AFAICT, though, *very* few people
>> use ecpg.
> With procedures we can be in conformance with ANSI standard and others
> databases.
[ shrug... ] If you want us to buy into supporting parts of the SQL spec
other than Part 2, you need to make a case why --- the argument that
"it's in the standard" cuts no ice at all with me for all that other
stuff. AFAICS the market demand for ecpg-style APIs is nil.
regards, tom lane
On 10/27/07, Gregory Stark <stark@enterprisedb.com> wrote: > > Most of that sounded to me like a proposal to re-invent ecpg. If there > > were such a large demand for doing things that way, there would be many > > more users of ecpg than bare libpq. AFAICT, though, *very* few people > > use ecpg. > > ecpg is a client-side thing though, isn't it? So, for example if you are > writing some large batch job for which you want to process many records, > occasionally updating some of them you would end up having to download all the > data to the client. > > I think what people want is something like plpgsql, ie, it runs on the server > and can access the data without having to marshal and unmarshal it over the > wire to a client. They just want to be able to use plpgsql outside of a > transaction so they can start and commit transactions within the execution > context of the PL execution environment. > > And they want to program it using a procedural style, with mutable per-session > variables storing state. It's not clear to me whether those variables should > be transactional but I don't think most people expect them to be. They expect > cheap per-session non-transactional variables which have no additional > overhead over plpgsql variables but can be referenced easily from any SQL so > if the variable's value is change the meaning of their SQL magically changes. IMHO, you are right on the money with all your points. The major point of procedures is manual transaction management...this would allow folding into the database many things that are now only possible though the protocol. Mutable session variables would be nice, but I'll take a plpgsql langauge (or psm) with or without them, so long as transactions are manual. It's possible to emulate variables using scalar functions with the desired volatility currently (but you still have to be careful with transactions). merlin
2007/10/27, Tom Lane <tgl@sss.pgh.pa.us>:
> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
> > 2007/10/27, Tom Lane <tgl@sss.pgh.pa.us>:
> >> Most of that sounded to me like a proposal to re-invent ecpg. If there
> >> were such a large demand for doing things that way, there would be many
> >> more users of ecpg than bare libpq. AFAICT, though, *very* few people
> >> use ecpg.
>
> > With procedures we can be in conformance with ANSI standard and others
> > databases.
>
> [ shrug... ] If you want us to buy into supporting parts of the SQL spec
> other than Part 2, you need to make a case why --- the argument that
> "it's in the standard" cuts no ice at all with me for all that other
> stuff. AFAICS the market demand for ecpg-style APIs is nil.
>
My goal is well support of SQL/PSM and well support of stored
procedures. Conformance with ANSI is nice secondary effect.
Actually, current model of OUT params is dificult for learning, for
develop too (in C), and it's rare. I like it for functions, it is
really good idea, but isnot easy (and sometimes is limiting (in
overloading))
I cannot do:
CREATE PROCEDURE foo(IN a int, OUT b varchar)
CREATE PROCEDURE foo(IN a int, OUT b integer)
sample:
CREATE FUNCTION foo(out a int, out b int)
BEGIN a := 10; b := 30;
END;
CREATE FUN caller(out a int, out b int)
BEGIN SELECT INTO a,b foo()
END;
Try to write these function in C.
With procedures it can be:
int
foo(PG_PROCEDURE_CALL)
{ PG_SETARG_INT32(Int32GetDatum(10)); PG_SETARG_INT32(Int32GetDatum(30));
return 0; /* exit status */
}
int
caller(PG_FUNCTION_CALL)
{ heapTuple .....
if (0 == DirectProcedureCall(DATUMBYREF(&a), DATUMBYREF(&b))) {
} ...
Regards
Pavel Stehule
Merlin, Pavel, > Mutable session variables would be nice, but I'll take a plpgsql > langauge (or psm) with or without them, so long as transactions are > manual. It's possible to emulate variables using scalar functions > with the desired volatility currently (but you still have to be > careful with transactions). The other big useful feature we're missing from Functions is multisets. Not only would they be generally useful for SP programming, but multisets would eliminate one of the big hurdles in re-writing T-SQL stored procedures in PG, and thus make it easier to port from SQL Server. You don't hear a lot of demand for multisets on the mailing lists because we're not getting those SQL Server / Sybase crossovers now. Of course, Pavel can implement one of these features at a time ... -- Josh Berkus PostgreSQL Lead Sun Microsystems San Francisco 01-415-752-2500
Ühel kenal päeval, L, 2007-10-27 kell 13:43, kirjutas Gregory Stark: > "Pavel Stehule" <pavel.stehule@gmail.com> writes: > > > Later: > > * procedure can manages transactions, > > Personally this is the only actual feature on the list that seems to have any > point to me. Same here. And I'd like it to have support for other PL's beside SQL and PL/PGSQL. > * procedure can be overloaded, How is this different from function overloading ? > * procedure can not returns recordset or multi recordset, Why ? > * procedure doesn't support default parameters, Currently we support kind-of "default parameters" at the end of arg list by defining a function with only non-default params, which calls the longer funtion with default params filled in. ------------- Hannu
Ühel kenal päeval, L, 2007-10-27 kell 12:55, kirjutas Josh Berkus: > Merlin, Pavel, > > > Mutable session variables would be nice, but I'll take a plpgsql > > langauge (or psm) with or without them, so long as transactions are > > manual. It's possible to emulate variables using scalar functions > > with the desired volatility currently (but you still have to be > > careful with transactions). > > The other big useful feature we're missing from Functions is multisets. I think that support for multisets has been removed from our fe-be protocol implementation bit-by-bit. --------- Hannu
On Sun, Oct 28, 2007 at 12:05:26AM +0300, Hannu Krosing wrote: > Ühel kenal päeval, L, 2007-10-27 kell 12:55, kirjutas Josh Berkus: > > Merlin, Pavel, > > > > > Mutable session variables would be nice, but I'll take a plpgsql > > > langauge (or psm) with or without them, so long as transactions > > > are manual. It's possible to emulate variables using scalar > > > functions with the desired volatility currently (but you still > > > have to be careful with transactions). > > > > The other big useful feature we're missing from Functions is > > multisets. > > I think that support for multisets has been removed from our fe-be > protocol implementation bit-by-bit. How do you mean? The only way I've done multisets is by creating functions that return multiple refcursors, either in a row or as SETOF. Is or was there some other way? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Hello three years ago was big discus about stored procedures. Three years later postgresql does not support stored procedures. So instead of one super big jump I would to do more small steps. 2007/10/27, Hannu Krosing <hannu@skype.net>: > Ühel kenal päeval, L, 2007-10-27 kell 13:43, kirjutas Gregory Stark: > > "Pavel Stehule" <pavel.stehule@gmail.com> writes: > > > > > Later: > > > * procedure can manages transactions, > > > > Personally this is the only actual feature on the list that seems to have any > > point to me. > > Same here. > > And I'd like it to have support for other PL's beside SQL and PL/PGSQL. > > > * procedure can be overloaded, > > How is this different from function overloading ? Current functions can overload only with IN params, stored procedures can be overloaded with OUT params too. > > > * procedure can not returns recordset or multi recordset, > > Why ? > next step, Pavel
On 10/27/07, David Fetter <david@fetter.org> wrote: > The only way I've done multisets is by creating functions that return > multiple refcursors, either in a row or as SETOF. Is or was there > some other way? <ahem>...arrays of composite type :-) merlin
On Sun, Oct 28, 2007 at 08:18:04PM -0500, Merlin Moncure wrote: > On 10/27/07, David Fetter <david@fetter.org> wrote: > > The only way I've done multisets is by creating functions that > > return multiple refcursors, either in a row or as SETOF. Is or > > was there some other way? > > <ahem>...arrays of composite type :-) That's not a multiset either in the T-SQL sense or in the sense in which the SQL standard defines MULTISET with COLLECT and FUSION. Cheers, David (who thinks those might be cool, too) -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Ühel kenal päeval, L, 2007-10-27 kell 14:10, kirjutas David Fetter: > On Sun, Oct 28, 2007 at 12:05:26AM +0300, Hannu Krosing wrote: > > Ühel kenal päeval, L, 2007-10-27 kell 12:55, kirjutas Josh Berkus: > > > Merlin, Pavel, > > > > > > > Mutable session variables would be nice, but I'll take a plpgsql > > > > langauge (or psm) with or without them, so long as transactions > > > > are manual. It's possible to emulate variables using scalar > > > > functions with the desired volatility currently (but you still > > > > have to be careful with transactions). > > > > > > The other big useful feature we're missing from Functions is > > > multisets. > > > > I think that support for multisets has been removed from our fe-be > > protocol implementation bit-by-bit. > > How do you mean? > > The only way I've done multisets is by creating functions that return > multiple refcursors, either in a row or as SETOF. Is or was there > some other way? I _think_ that originally an SQL function with multiple SELECTs was meant to return results for all these in a row, as a multiset. I don't think that this has ever been the case, at least not after switch from Postgres 4.2 Quel to Postgres95 SQL. What I was referring to, was a "code cleanup" of libpq several years ago, when someone (maybe Bruce IIRC) removed ability to accept multiple recordsets from backend altogether, on the basis that it is not used anyway. ---------------- Hannu
"Hannu Krosing" <hannu@skype.net> writes: > What I was referring to, was a "code cleanup" of libpq several years > ago, when someone (maybe Bruce IIRC) removed ability to accept multiple > recordsets from backend altogether, on the basis that it is not used > anyway. You can still receive multiple record sets just fine using libpq. psql doesn't handle them but they're there. When I was doing the concurrent psql patch I also had it handling multiple record sets. Something else you may be thinking of, I don't think it's legal to do queries like "select 1 ; select 2" in the new protocol. That was legal in the old protocol. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!
Gregory Stark wrote: > "Hannu Krosing" <hannu@skype.net> writes: > > > What I was referring to, was a "code cleanup" of libpq several years > > ago, when someone (maybe Bruce IIRC) removed ability to accept multiple > > recordsets from backend altogether, on the basis that it is not used > > anyway. > > You can still receive multiple record sets just fine using libpq. psql doesn't > handle them but they're there. When I was doing the concurrent psql patch I > also had it handling multiple record sets. > > Something else you may be thinking of, I don't think it's legal to do queries > like "select 1 ; select 2" in the new protocol. That was legal in the old > protocol. I think the cool thing that Josh Berkus wants is return query select a, b, c from foo; return query select d, e, f from bar; in a plpgsql function, and getting two result sets (I'm fuzzy about the exact syntax but you get the idea). Can this be done at all? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Josh Berkus wrote: > Not only would they be generally useful for SP programming, but multisets > would eliminate one of the big hurdles in re-writing T-SQL stored > procedures in PG, and thus make it easier to port from SQL Server. You > don't hear a lot of demand for multisets on the mailing lists because > we're not getting those SQL Server / Sybase crossovers now. > Its true that multiple result sets are a big deal with T-SQL programming: but I think you'll also need to provide a way for the locking model to behave in a similar way and also very importantly to be able to emulate the after-statement triggers view of new and old images. James
On Mon, Oct 29, 2007 at 07:32:11PM -0300, Alvaro Herrera wrote:
> Gregory Stark wrote:
> > "Hannu Krosing" <hannu@skype.net> writes:
> >
> > > What I was referring to, was a "code cleanup" of libpq several
> > > years ago, when someone (maybe Bruce IIRC) removed ability to
> > > accept multiple recordsets from backend altogether, on the basis
> > > that it is not used anyway.
> >
> > You can still receive multiple record sets just fine using libpq.
> > psql doesn't handle them but they're there. When I was doing the
> > concurrent psql patch I also had it handling multiple record sets.
> >
> > Something else you may be thinking of, I don't think it's legal to
> > do queries like "select 1 ; select 2" in the new protocol. That
> > was legal in the old protocol.
>
> I think the cool thing that Josh Berkus wants is
>
> return query select a, b, c from foo;
> return query select d, e, f from bar;
>
> in a plpgsql function, and getting two result sets (I'm fuzzy about the
> exact syntax but you get the idea). Can this be done at all?
Based on the example in TFM for PL/PgSQL:
BEGIN;
CREATE TABLE foo(foo_id SERIAL PRIMARY KEY, foo_text TEXT);
CREATE TABLE bar(bar_id SERIAL PRIMARY KEY, bar_text TEXT);
INSERT INTO foo(foo_text) VALUES ('a'),('b'),('c'),('d');
INSERT INTO bar(bar_text) VALUES ('e'),('f'),('g'),('h');
CREATE FUNCTION wtf(refcursor, refcursor)
RETURNS SETOF refcursor
LANGUAGE plpgsql
AS $$
BEGIN OPEN $1 FOR SELECT * FROM foo; RETURN NEXT $1; OPEN $2 FOR SELECT * FROM bar; RETURN NEXT $2;
END;
$$;
SELECT * FROM wtf('a','b');
FETCH all FROM a;
FETCH all FROM b;
ROLLBACK;
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
2007/10/29, Alvaro Herrera <alvherre@commandprompt.com>: > Gregory Stark wrote: > > "Hannu Krosing" <hannu@skype.net> writes: > > > > > What I was referring to, was a "code cleanup" of libpq several years > > > ago, when someone (maybe Bruce IIRC) removed ability to accept multiple > > > recordsets from backend altogether, on the basis that it is not used > > > anyway. > > > > You can still receive multiple record sets just fine using libpq. psql doesn't > > handle them but they're there. When I was doing the concurrent psql patch I > > also had it handling multiple record sets. > > > > Something else you may be thinking of, I don't think it's legal to do queries > > like "select 1 ; select 2" in the new protocol. That was legal in the old > > protocol. > > I think the cool thing that Josh Berkus wants is > > return query select a, b, c from foo; > return query select d, e, f from bar; > maybe better SELECT a,b FROM foo; SELECT d, e FROM bar; procedure doesn't need return statement usually
2007/10/30, David Fetter <david@fetter.org>:
> On Mon, Oct 29, 2007 at 07:32:11PM -0300, Alvaro Herrera wrote:
> > Gregory Stark wrote:
> > > "Hannu Krosing" <hannu@skype.net> writes:
> > >
> > > > What I was referring to, was a "code cleanup" of libpq several
> > > > years ago, when someone (maybe Bruce IIRC) removed ability to
> > > > accept multiple recordsets from backend altogether, on the basis
> > > > that it is not used anyway.
> > >
> > > You can still receive multiple record sets just fine using libpq.
> > > psql doesn't handle them but they're there. When I was doing the
> > > concurrent psql patch I also had it handling multiple record sets.
> > >
> > > Something else you may be thinking of, I don't think it's legal to
> > > do queries like "select 1 ; select 2" in the new protocol. That
> > > was legal in the old protocol.
> >
> > I think the cool thing that Josh Berkus wants is
> >
> > return query select a, b, c from foo;
> > return query select d, e, f from bar;
> >
> > in a plpgsql function, and getting two result sets (I'm fuzzy about the
> > exact syntax but you get the idea). Can this be done at all?
>
> Based on the example in TFM for PL/PgSQL:
>
> BEGIN;
> CREATE TABLE foo(foo_id SERIAL PRIMARY KEY, foo_text TEXT);
> CREATE TABLE bar(bar_id SERIAL PRIMARY KEY, bar_text TEXT);
> INSERT INTO foo(foo_text) VALUES ('a'),('b'),('c'),('d');
> INSERT INTO bar(bar_text) VALUES ('e'),('f'),('g'),('h');
> CREATE FUNCTION wtf(refcursor, refcursor)
> RETURNS SETOF refcursor
> LANGUAGE plpgsql
> AS $$
> BEGIN
> OPEN $1 FOR SELECT * FROM foo;
> RETURN NEXT $1;
> OPEN $2 FOR SELECT * FROM bar;
> RETURN NEXT $2;
> END;
> $$;
> SELECT * FROM wtf('a','b');
> FETCH all FROM a;
> FETCH all FROM b;
> ROLLBACK;
>
disavantages
* it is transaction based, so you have to wait for first set untill
function is completed (it's avantage too, but you havn't choice now)
* too much lines, for simple task
On Tue, Oct 30, 2007 at 09:48:24AM +0100, Pavel Stehule wrote:
> 2007/10/30, David Fetter <david@fetter.org>:
> > Based on the example in TFM for PL/PgSQL:
> >
> > BEGIN;
> > CREATE TABLE foo(foo_id SERIAL PRIMARY KEY, foo_text TEXT);
> > CREATE TABLE bar(bar_id SERIAL PRIMARY KEY, bar_text TEXT);
> > INSERT INTO foo(foo_text) VALUES ('a'),('b'),('c'),('d');
> > INSERT INTO bar(bar_text) VALUES ('e'),('f'),('g'),('h');
> > CREATE FUNCTION wtf(refcursor, refcursor)
> > RETURNS SETOF refcursor
> > LANGUAGE plpgsql
> > AS $$
> > BEGIN
> > OPEN $1 FOR SELECT * FROM foo;
> > RETURN NEXT $1;
> > OPEN $2 FOR SELECT * FROM bar;
> > RETURN NEXT $2;
> > END;
> > $$;
> > SELECT * FROM wtf('a','b');
> > FETCH all FROM a;
> > FETCH all FROM b;
> > ROLLBACK;
> >
>
> disavantages
> * it is transaction based, so you have to wait for first set untill
> function is completed (it's avantage too, but you havn't choice now)
>
> * too much lines, for simple task
I'm not saying we don't need it. I was just illustrating that it's
currently possible to return multiple result sets.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter wrote: > AS $$ > BEGIN > OPEN $1 FOR SELECT * FROM foo; > RETURN NEXT $1; > OPEN $2 FOR SELECT * FROM bar; > RETURN NEXT $2; > END; > $$; I know it. It is ugly as all hell. -- Alvaro Herrera http://www.advogato.org/person/alvherre "El dÃa que dejes de cambiar dejarás de vivir"
James Mansion wrote: > Josh Berkus wrote: >> Not only would they be generally useful for SP programming, but >> multisets would eliminate one of the big hurdles in re-writing T-SQL >> stored procedures in PG, and thus make it easier to port from SQL >> Server. You don't hear a lot of demand for multisets on the mailing >> lists because we're not getting those SQL Server / Sybase crossovers >> now. >> > Its true that multiple result sets are a big deal with T-SQL > programming: but I think you'll also need to provide a way for the > locking model to behave in a similar way and also very importantly to > be able to emulate the after-statement triggers view of new and old > images. I don't think we need to (or, for that matter, are able to) change the locking model, but the NEW and OLD views of for-statement triggers should be just a SMOP. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> > I think the cool thing that Josh Berkus wants is > > > > return query select a, b, c from foo; > > return query select d, e, f from bar; > > > > maybe better > > SELECT a,b FROM foo; > SELECT d, e FROM bar; > > procedure doesn't need return statement usually The background for Quel was, that when selecting all fields from an inheritance hierarchy you got the additional fields of each child. Thus the field count and types could vary within one cursor. Like if you would allow the following: select a, b::int from foo union all select a, c::varchar, d, e from bar I don't think anybody would want to transfer that idea to sql clients. In sql the first statement would define field count, name/alias and type. The second statement would need to implicitly cast or fail if it does not match. Andreas
On Tue, Oct 30, 2007 at 11:31:12AM -0300, Alvaro Herrera wrote: > David Fetter wrote: > > > AS $$ > > BEGIN > > OPEN $1 FOR SELECT * FROM foo; > > RETURN NEXT $1; > > OPEN $2 FOR SELECT * FROM bar; > > RETURN NEXT $2; > > END; > > $$; > > I know it. It is ugly as all hell. Agreed. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Oct 30, 2007 at 11:33:19AM -0300, Alvaro Herrera wrote: > James Mansion wrote: > > Josh Berkus wrote: > >> Not only would they be generally useful for SP programming, but > >> multisets would eliminate one of the big hurdles in re-writing > >> T-SQL stored procedures in PG, and thus make it easier to port > >> from SQL Server. You don't hear a lot of demand for multisets on > >> the mailing lists because we're not getting those SQL Server / > >> Sybase crossovers now. > >> > > Its true that multiple result sets are a big deal with T-SQL > > programming: but I think you'll also need to provide a way for the > > locking model to behave in a similar way and also very importantly > > to be able to emulate the after-statement triggers view of new and > > old images. > > I don't think we need to (or, for that matter, are able to) change > the locking model, but the NEW and OLD views of for-statement > triggers should be just a SMOP. Having NEW and OLD views of per-statement triggers would be a Very Nice Feature(TM) independent of stored procedures. For one thing, it would make certain kinds of replication trivial. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Oct 30, 2007 at 03:24:20PM +0100, Zeugswetter Andreas ADI SD wrote: > > > I think the cool thing that Josh Berkus wants is > > > > > > return query select a, b, c from foo; > > > return query select d, e, f from bar; > > > > > > > maybe better > > > > SELECT a,b FROM foo; > > SELECT d, e FROM bar; > > > > procedure doesn't need return statement usually > > The background for Quel was, that when selecting all fields from an > inheritance hierarchy you got the additional fields of each child. > > Thus the field count and types could vary within one cursor. > Like if you would allow the following: > select a, b::int from foo > union all > select a, c::varchar, d, e from bar This is called jagged rows, which were in Illustra, Informix, and possibly some others. It would be nice to have protocol-level support for them, but it's a pretty large feature because the current Postgres code starts off with the assumption that you know at run time the shape of all the rows and that that shape is uniform. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On 10/30/07, Zeugswetter Andreas ADI SD > The background for Quel was, that when selecting all fields from > an inheritance hierarchy you got the additional fields of each child. > > Thus the field count and types could vary within one cursor. > Like if you would allow the following: > select a, b::int from foo > union all > select a, c::varchar, d, e from bar > > I don't think anybody would want to transfer that idea to sql clients. > In sql the first statement would define field count, name/alias and > type. > The second statement would need to implicitly cast or fail if it does > not match. Arrays of composites, along with aggregation tricks, can give you similar features. The syntax is wierd but powerful in cases like this. Array support over the protocol and on the client side is lacking but that's a different topic. That said, returning _complex_ sets is a different problem from returning multiple sets. merlin
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --------------------------------------------------------------------------- Pavel Stehule wrote: > Hello, > > I found lot of discus about this topic. > > http://www.postgresql.org/docs/faqs.TODO.html > http://archives.postgresql.org/pgsql-hackers/2003-08/msg00501.php > http://archives.postgresql.org/pgsql-hackers/2004-09/msg00734.php > http://archives.postgresql.org/pgsql-hackers/2004-08/msg00872.php > http://archives.postgresql.org/pgsql-hackers/2004-09/msg00702.php > > There is one result - OUT params for functions. I propose start with > simple goals that we can enhance in future. > > First goal: Procedures support for better conformance with ANSI SQL: > > * procedure returns any only through OUT, INOUT params, > * procedure has own executor, that allows byref params (and own > transaction management in future), > * procedure can be overloaded, > * procedure can not returns recordset or multi recordset, > * procedure doesn't support default parameters, > * SQL statement CALL allows only expression (this proposal doesn't > need session variables) for older environments > * new SPI_exec_procedures API (allows binding to host variables) and > some similar in libpq, that allow CALL implementation in pgsql and > others. > * new internal exec_exec_proc (allow ref on datum variable) used in > plpgsql statement CALL. > * new V2 calling convention (maybe based on SQL/CLI) > * no changes in current functions support > > Later: > * procedure can manages transactions, > * procedure can returns recordset or multi recordset, > * procedure allows default parameters, > * CALL statement allows session variables > * no changes in current functions support > > Why new calling convention? I would to support byref variables and > then I have to carry memory context info ... and maybe some others > > Nice a weekend > > Pavel Stehule > > p.s. > > Why procedures? New parts of ANSI SQL use it, and what is worse, they > use methods: > http://www.wiscorp.com/H2-2005-350-WG4-wlg005-Part-7-History-2nd-Edition-Text-for-Working-Draft.pdf > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Added to TODO: * Allow functions to control the transaction state http://archives.postgresql.org/pgsql-hackers/2007-10/msg01375.php --------------------------------------------------------------------------- Pavel Stehule wrote: > Hello, > > I found lot of discus about this topic. > > http://www.postgresql.org/docs/faqs.TODO.html > http://archives.postgresql.org/pgsql-hackers/2003-08/msg00501.php > http://archives.postgresql.org/pgsql-hackers/2004-09/msg00734.php > http://archives.postgresql.org/pgsql-hackers/2004-08/msg00872.php > http://archives.postgresql.org/pgsql-hackers/2004-09/msg00702.php > > There is one result - OUT params for functions. I propose start with > simple goals that we can enhance in future. > > First goal: Procedures support for better conformance with ANSI SQL: > > * procedure returns any only through OUT, INOUT params, > * procedure has own executor, that allows byref params (and own > transaction management in future), > * procedure can be overloaded, > * procedure can not returns recordset or multi recordset, > * procedure doesn't support default parameters, > * SQL statement CALL allows only expression (this proposal doesn't > need session variables) for older environments > * new SPI_exec_procedures API (allows binding to host variables) and > some similar in libpq, that allow CALL implementation in pgsql and > others. > * new internal exec_exec_proc (allow ref on datum variable) used in > plpgsql statement CALL. > * new V2 calling convention (maybe based on SQL/CLI) > * no changes in current functions support > > Later: > * procedure can manages transactions, > * procedure can returns recordset or multi recordset, > * procedure allows default parameters, > * CALL statement allows session variables > * no changes in current functions support > > Why new calling convention? I would to support byref variables and > then I have to carry memory context info ... and maybe some others > > Nice a weekend > > Pavel Stehule > > p.s. > > Why procedures? New parts of ANSI SQL use it, and what is worse, they > use methods: > http://www.wiscorp.com/H2-2005-350-WG4-wlg005-Part-7-History-2nd-Edition-Text-for-Working-Draft.pdf > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +