On Mon, Oct 13, 2008 at 8:09 AM, Vladimir Dzhuvinov <vd@xxxxxxxxx> wrote: >>> I came across a blog post of yours ( >>> http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html >>> ) as well as several list posts indicating that multiple result sets >>> might be in the working. Should I check the situation again when 8.4 is >>> released? > >> I have only very raw prototype, so I am sure, so this feature will not >> be in 8.4, and I am not sure about 8.5. It's nice feature, but I am >> not force to complete and clean code, and I am not able create patch. >> If you would do it, I am, with pleasure, send you source code, that >> allows multirecord sets. > > Yes, I'll be glad to examine your patch. At least to get an idea of > what's involved in implementing multiple result sets. Stored procedure support is a pretty complicated feature. They differ with functions in two major areas: *) input/output syntax. this is what you are dealing with *) manual transaction management. stored procedures should allow you emit 'BEGIN/COMMIT' and do things like vacuum. IIRC, I don't think there was a consensus on the second point or if it was ok to implement the syntax issues without worrying about transactions. I'll give you two other strategies for dealing with multiple result sets in pl/pgsql: *) temp tables: it's very easy to create/dump/drop temp tables and use them in later transactions. previous to 8.3 though, doing it this way was a pain because of plan invalidation issues. *) arrays of composites (8.2+) create table foo(a int, b int, c int); create table bar(a text, b text, c text); pl/sql: create function foobar(foos out foo[], bars out bar[]) returns record as $$ select (select array(select foo from foo)), (select array(select bar from bar)); $$ language sql; pl/pgsql: create function foobar(foos out foo[], bars out bar[]) returns record as $$ begin foos := array(select foo from foo); bars := array(select bar from bar); return; end; $$ language plpgsql; select foos[1].b from foobar(); Customize the above to taste. For example you may want to return the array dims. By the way, if you are writing client side code in C, you may want to look at libpqtypes (http://libpqtypes.esilo.com/)...it makes dealing with arrays and composites on the client sides much easier. For 8.3 though it requires a patched libpq. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
