On Sun, Nov 20, 2005 at 06:05:36PM +0100, Pavel Stehule wrote:
> 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.

You're confusing syntax with implementation. Internally the functions
would return a single tuple at a time. But there is no real reason why:

return table (select * from foo);

would not simply loop and return each tuple. We can create syntax as we
feel appropriate if we think it makes thing easier. Thus internally
your two variants would both work and do the same thing. Look at the
SQL language functions. There if you say 'select * from foo' it returns
the whole table without a loop...

Anyway, this discussion isn't really going to go anywhere without some
code. I'll see what I can do.

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

iD8DBQFDgL3oIB7bNG8LQkwRAghAAJ94oL0pUXP0XOsjKVmBmV HI6mUNsACggsfY
mKqckXFv+S2An0fJpsMjNAI=
=GKEf
-----END PGP SIGNATURE-----

Hello

I thinking about solution based on setof cursors. This solustion has three
big minus:
1. I can "unpack" cursors after finish of called procedure. If I get
exception, or long query, I can show nothing.
2. Old clients don't understand and don't unpack cursor. Statement call is
(+/-) == statement SELECT (more if call return only one table).
3. twice communication.

backend client
==============
<--------------------- call
-----------------------> cursors
<---------------------- select cursor
------------------------> table

this isn't pretty solution. May be with minimal changes in code. I think,
this need bigger changes and support next class of stored objects.

best regards
Pavel Stehule

__________________________________________________ _______________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.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

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Sun, Nov 20, 2005 at 11:29:39AM -0500, Tom Lane wrote:
>> That only works if the caller is prepared to read each result serially,
>> and not (say) a row at a time in parallel.

> Urk! I don't think anyone is suggesting that resultsets can be
> interleaved.

No? If not, why not? The main reason why this is being pushed, IIRC,
is the claim that "you can do this easily in other databases". If you
don't want to support interleaved retrieval of multiple datasets, you
had better be prepared to prove that no other popular database can
do it either.

>> 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.

This is not about what we can do today with PG functions; it's about
what can be done with a stored procedure in other RDBMSes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Sun, Nov 20, 2005 at 03:41:36PM -0500, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > Urk! I don't think anyone is suggesting that resultsets can be
> > interleaved.
>
> No? If not, why not? The main reason why this is being pushed, IIRC,
> is the claim that "you can do this easily in other databases". If you
> don't want to support interleaved retrieval of multiple datasets, you
> had better be prepared to prove that no other popular database can
> do it either.

I don't know if I can prove it. however, I do have a few datapoints:

1. In SQLJ when you call a stored procedure that returns multiple
datasets, you have to close a resultset before you can start on the
next one.

: Result sets are returned to the calling program in the same order
: that their cursors are opened in the stored procedure. When there are
: no more result sets to retrieve, getNextResultSet returns a null
: value.

http://publib.boulder.ibm.com/infoce...bjnkmstr81.htm

2. ASP seems to have the same restriction

http://www.w3schools.com/ado/met_rs_nextrecordset.asp

Note, we should distinguish here between (a) being able to send a query
before you've retreived all the data of the current one and having the
results of those interleaved, and (b) having the results of a single
query return two results sets interleaved.

I beleive the first is supported by other DBs but not us (other than
explicit cursors). I don't think any support the latter, but I can't
claim to have checked them all. Your point is taken though, I'll see if
I can find any evidence one way or the other.

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

iD8DBQFDgOsEIB7bNG8LQkwRAtoYAJ9CzWoq4/1p0g1RVKzgmgab+cwuSQCgjzSx
Jgl6Uo7cQdyBOTnbIhN7MBA=
=2tnE
-----END PGP SIGNATURE-----

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.
>
> 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.

Is there a TODO here?

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(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