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: [email protected]
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
