On Friday, March 02, 2012 05:37:40 PM Adrian Klaver wrote:

> On Friday, March 02, 2012 4:54:35 pm Ed Leafe wrote:

> > On Mar 2, 2012, at 6:42 PM, Adrian Klaver wrote:

> > > The query passed to the cursor would do the processing on the server

> > > and return the filtered down results back to Dabo. Temporary tables

> > > exist for the life of the connection generally, so they would be

> > > available to each DataSet cursor.

> >

> > I must have misunderstood your post. When you said to substitute

> > DataSet

> >

> > (local) for TempTable (remote), I thought that you meant to pull the

> > temp

> > table into a Dabo dataset, and then query against that.

>

> Yea, I could have been clearer. I think of DataSets as temporary tables when

> working with them. So what I was actually trying to say was a temporary

> table(DataSet) over a temporary table(database). I made this leap in my

> mind without explaining it to my fingers as I typed:(

>

> So in the case Ricardo shows something like(warning pseudo code):

>

> cur=tempCursor("select *

> into #TempTable

> from someBigTable

> left join someOtherTable

> on someConditions

> where someRestraints")

>

> cur1=tempCursor("select someField, someOtherField, sum(someValue) as 'Total'

> from #TempTable

> group by someField, someOtherField

> order by someField, someOtherField")

>

> cur2=tempCursor("select *

> from #TempTable

> left join #OtherTempTableFromSomeOtherPreviousQuery

> on SomeConditions

> where someOtherRestraints")

>

> Where #OtherTempTableFromSomeOtherPreviousQuery was set up previously.

This has been a very interesting discussion. I kept thinking about the ms-sql

server being able to return more than one cursor. So I checked to see if it

was possible with Postgres. I did find something close:

The following example shows one way to return multiple cursors from a single

function:

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$

BEGIN

OPEN $1 FOR SELECT * FROM table_1;

RETURN NEXT $1;

OPEN $2 FOR SELECT * FROM table_2;

RETURN NEXT $2;

END;

$$ LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.

BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;

FETCH ALL FROM b;

COMMIT;

I really enjoy programming - everyday I learn something!

This is of course is different than creating a temp table that lives as long

as the session lives.

Johnf

_______________________________________________

Post Messages to: Dabo-users@leafe.com

Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users

Searchable Archives: http://leafe.com/archives/search/dabo-users

This message: http://leafe.com/archives/byMID/2901470.aUbqjFCztB@linux-12