SEO

I've been thinking about this and wondered if this is a way to get it
done without too much work.

1. Create an "anyrecord" type to which any record type can be cast.
It's essentially a heaptuple with a tupledesc.

2. "anyrecord" is opaque to the parser, you cannot dereference it, only
output it or pass it to other functions accepting "anyrecord". Possibly
some dynamic languages may be able to reference these.

3. In the output functions printtup_startup/printtup, if any of the
result fields are of type "anyrecord" it defers the 'T' message until
the tuples arrive. When they do it expands the anyrecord and creates
columns from each and sends an appropriate 'T' message.

4. libpq already supports multiple result sets so no problem there.

The effect of this would be that you get multiple result sets, once for
each time the tupledesc changes. As for creating a split without
changing, maybe you need to return a special empty tuple which
signifies end-of-set.

It also occured to me we could just change the "record" type to do
this, but this would change the behaviour of:

test=# select x from test() as x;
x
-------
(1,2)
(1 row)

to:

a | b
---+---
1 | 2
(1 row)

But that's backward incompatable. OTOH, it would mean we could get rid
of the requirement that functions returning "record" must specify a
column definition list in the query. the only restriction is that you
can't dereference it, but that doesn't seem so big a deal.

So, kill a few birds with one stone. Any thoughts?
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDf0EmIB7bNG8LQkwRAlDvAJ9MaCk42vWBlDsmkkcQIV LdsttIlACghBWw
vQYiH+X/9RE93P4uDUFW97M=
=aqr5
-----END PGP SIGNATURE-----

Martijn van Oosterhout <kleptog@svana.org> writes:
> So, kill a few birds with one stone. Any thoughts?

I don't think any of this will actually work :-(. There's too much code
that assumes that all the tuples returned by a query are alike, and I
for one don't feel like trying to find and fix it all. (Not all of it
is within our control, either --- this will break client code along with
the backend.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Sat, Nov 19, 2005 at 12:43:15PM -0500, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > So, kill a few birds with one stone. Any thoughts?
>
> I don't think any of this will actually work :-(. There's too much code
> that assumes that all the tuples returned by a query are alike, and I
> for one don't feel like trying to find and fix it all. (Not all of it
> is within our control, either --- this will break client code along with
> the backend.)

I don't think so, as far as all the functions are concerned, the tuples
are all the same: when a function is called with anyrecord, it's passed
a single argument, the heaptuple+tupledesc. It's an opaque verlena type
that nothing is going to be able to access unless they actually go to
the effort.

All this does is essentially flatten records-in-tuples in the output
function.

Consider:

create function a(anyrecord) returns anyrecord;
create function b(int4) returns anyrecord;

select a(b(2));

Does anything in the backend other than those two functions need to
know the exact format of the "anyrecord"? Even if the actual records
contain 20 values, to everybody else it's just an opaque verlena type.
And in the output (and *only* on output), the printtup function can
examine the tupledesc to tell the client what data to expect.

Seems like it should be possible to me. Another way to put it would be
making records a first-class type. What am I missing?

Thanks in advance,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDf2jBIB7bNG8LQkwRAjCHAJ4rVzqTIJ3x+hiaz8FBpe OVI0U7zgCcCZ5s
AMTpR6R9E0CPjY5Omb8KJUE=
=t2Ef
-----END PGP SIGNATURE-----

Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > So, kill a few birds with one stone. Any thoughts?
>
> I don't think any of this will actually work :-(. There's too much code
> that assumes that all the tuples returned by a query are alike, and I
> for one don't feel like trying to find and fix it all. (Not all of it
> is within our control, either --- this will break client code along with
> the backend.)

Hmm -- probably we could declare that the current libpq API will not
support multiple result sets from one query, and return only the first
one to the application discarding the rest. (It just occured to me --
what happens if one send multiple SELECTs in a semicolon-separated query
via libpq?). New apps wanting to take advantage of the new
functionality would need to invoke a different function.

At the protocol level this will need an extension anyway, so clients
using the protocol directly would need to be updated to understand
multiple results.

I know people migrating from SQL Server (maybe others?) are already
having trouble because of our inability to return multiple result sets.
The sooner we do it, the sooner all the code will be fixed ...

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

>
>Consider:
>
>create function a(anyrecord) returns anyrecord;
>create function b(int4) returns anyrecord;
>
>select a(b(2));
>

for my task I need little different form :-(

create function a(..) returns setof tables

but SQL2003 needs type table, and this can be solution

__________________________________________________ _______________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Sun, Nov 20, 2005 at 08:43:05AM +0100, Pavel Stehule wrote:
> >Consider:
> >
> >create function a(anyrecord) returns anyrecord;
> >create function b(int4) returns anyrecord;
> >
> >select a(b(2));
> >
>
> for my task I need little different form :-(
>
> create function a(..) returns setof tables
>
> but SQL2003 needs type table, and this can be solution

You want a function return entire tables at a time? Why bother when you
can just return rows and signal when the next table starts?

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDgIOUIB7bNG8LQkwRAgrOAJ9ihFNgbriWgM4ouNB0FX nbyZTRCgCfbc2R
gt1mV9KqCyZKZKs2DtAojCU=
=ZC3N
-----END PGP SIGNATURE-----

On Sat, Nov 19, 2005 at 08:02:08PM -0300, Alvaro Herrera wrote:
> Hmm -- probably we could declare that the current libpq API will not
> support multiple result sets from one query, and return only the first
> one to the application discarding the rest. (It just occured to me --
> what happens if one send multiple SELECTs in a semicolon-separated query
> via libpq?). New apps wanting to take advantage of the new
> functionality would need to invoke a different function.

libpq supports it just fine. You do a PQsendQuery() and then as many
PQgetResult()s as it takes to get back the results. This worked for a
while AFAIK.

> At the protocol level this will need an extension anyway, so clients
> using the protocol directly would need to be updated to understand
> multiple results.

The protocol supports it fine. If the server sends a new 'T' record,
libpq assumes it's returning a new resultset. When the server sends a
"ready for query" message, libpq knows that the last resultset has
arrived.

It's the backend that needs work, not the protocol.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDgIMUIB7bNG8LQkwRAnXcAJ43q5M+RGVaWecszQc71x 2Fvj3JIgCffoOn
GBJcjiWvHLs9sognM4f3qqE=
=79YR
-----END PGP SIGNATURE-----

Martijn van Oosterhout <kleptog@svana.org> writes:
> libpq supports it just fine. You do a PQsendQuery() and then as many
> PQgetResult()s as it takes to get back the results. This worked for a
> while AFAIK.

That only works if the caller is prepared to read each result serially,
and not (say) a row at a time in parallel. There are a bunch of
ease-of-use problems as well, such as knowing which resultset is which,
coping with errors detected after the first resultset(s) are sent, etc.

A more realistic way of dealing with multiple resultsets is to deliver
them as named cursor references and allow the client to FETCH
reasonable-sized chunks. We can sort of handle this today, but it's
notationally painful at both the stored-procedure and client ends.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> > for my task I need little different form :-(
> >
> > create function a(..) returns setof tables
> >
> > but SQL2003 needs type table, and this can be solution
>
>You want a function return entire tables at a time? Why bother when you
>can just return rows and signal when the next table starts?
>

what is difference between rows with different structures and tables? Tables
are more logic. But I unlike function which returns setof tables. This need
data type table. I prefere normal clasic solution.

-------------- stored proc -------------- | --------------- client
------------------

function -> scalar, vector, table

procedure -> OUT params
-----------------------------
every free select --------------------------------> table
-----------------------------

I don't have imagine how I can write readable code with your proposal


variants one:

create function aaa returns setof anyrecord
begin
for each a in select * from temptab1
return next a;
end loop;
return next 'next table';
for each a in select * from temptab2
return next a;
end loop;
return next 'ok';
return;
end;

variants two:
create procedure aaa(OUT allok bool)
begin
select * from temptab1;
select * from temptab2;
a := true;
end;

I don't have better words :-). I am sorry. I don't wont to complicate
internal structure of planer, executor, etc ... Procedures are different
than functions, and can be executed different, Isn't possible using
procedure in params list.

Nice day
Pavel

__________________________________________________ _______________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Sun, Nov 20, 2005 at 11:29:39AM -0500, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > libpq supports it just fine. You do a PQsendQuery() and then as many
> > PQgetResult()s as it takes to get back the results. This worked for a
> > while AFAIK.
>
> That only works if the caller is prepared to read each result serially,
> and not (say) a row at a time in parallel. There are a bunch of
> ease-of-use problems as well, such as knowing which resultset is which,
> coping with errors detected after the first resultset(s) are sent, etc.

Urk! I don't think anyone is suggesting that resultsets can be
interleaved. Apart from being extremely unlike the current model in
PostgreSQL, I can't think of a use for it that isn't served just as
well by sending them sequentially.

> A more realistic way of dealing with multiple resultsets is to deliver
> them as named cursor references and allow the client to FETCH
> reasonable-sized chunks. We can sort of handle this today, but it's
> notationally painful at both the stored-procedure and client ends.

But if you run a function, it can only return a single row at a time.
Fiddling with cursors means you would have to queue up. After all, the
function is going to return the tuples in a fixed order that is
independant of when the client asks.

At the end of the day, the client can only accept data in the order the
server sends it. Having to request each row seem somewhat inefficient.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD4DBQFDgLsDIB7bNG8LQkwRAtXMAJYk5U7wpavgv+QrY3VclS WCrFsrAKCP+fnG
2flMzlREW4B9nJ6otux+WQ==
=+vgj
-----END PGP SIGNATURE-----