Skip site navigation (1) Skip section navigation (2)

Re: Brio returning data through stored procedures.

From: David Gardner <david(dot)gardner(at)yucaipaco(dot)com>
To: Doug Thom <dthom(at)iagr(dot)net>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: Brio returning data through stored procedures.
Date: 2007-04-06 16:42:12
Message-ID: 46167864.9090609@yucaipaco.com (view raw or flat)
Thread:
Lists: pgsql-odbc
Hmm, thats way beyond my usage of pgsql, and possibly better answered on
the pgsql-general mailing list.

Doug Thom wrote:
> I saw it in the docs which I thought was encouraging but I can't see to
> get it to do what I want.
> 
> We have a metadata engine that we wrote. It is a layer above our
> warehouse. A feature of it is that it writes queries for our users, ETL,
> etc.
> 
> We have a procedure that constructs the (sometimes quite complex) query
> and opens it as a cursor and returns it to Brio. Brio, through ODBC,
> then pulls all the results out of the cursor and we're done.
> 
> So I would be doing something like:
> 
> --Do the thing that builds the query.
> Select query
>   Into query-string
>   From table-that-has-query;
> 
> Open cursor for query-string;
> 
> Return cursor;
> --End pseudo code
> 
> In Oracle this is done with a ref cursor but maybe not in PG. I note
> there is a function type called 'sql' rather than 'plpgsql' and I'm
> trying that out.
> 
> If this triggers any other thoughts from anyone please share.
> 
> Thanks again for the help.
> 
> -Doug
> 
> -----Original Message-----
> From: David Gardner [mailto:david(dot)gardner(at)yucaipaco(dot)com] 
> Sent: Friday, April 06, 2007 12:16 PM
> To: Doug Thom; pgsql-odbc(at)postgresql(dot)org
> Subject: Re: [ODBC] Brio returning data through stored procedures.
> 
> Hmm are you doing something like:
> IF something THEN
> 	SELECT * FROM tblOne;
> ELSE
> 	SELECT * FROM tblTwo;
> 
> Not sure what a refcursor is, but it is mentioned in the docs:
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-cursors.html
> 
> 
> Doug Thom wrote:
>> Hello, David, Andreas,
>>
>> Thank you for the suggestions. The challenge is that I need a ref
> cursor
>> because it is some arbitrary amount of data coming back from the
> server
>> in a cursor. Your suggestion shows a defined row but I do not know
> what
>> the row looks like so I cannot define a type beforehand.
>>
>> However, your comments have me wondering if there is a different route
> I
>> can take. Is there anything that is a 'generic row of data'? I see the
>> type "record" and I'm going to give that a try.
>>
>> Thanks again for the prompt response and ideas!
>>
>> -Doug
>>
>> -----Original Message-----
>> From: Andreas [mailto:maps(dot)on(at)gmx(dot)net] 
>> Sent: Friday, April 06, 2007 9:50 AM
>> To: pgsql-odbc(at)postgresql(dot)org
>> Cc: Doug Thom
>> Subject: Re: [ODBC] Brio returning data through stored procedures.
>>
>> Hi Doug
>>
>> I dont know Brio either but keep in mind that tables and views are 
>> allready defined types which you could use here.
>>
>> So if your procedure just selects a few rows from a view or table but 
>> should return all collumns from the source you don't have to define a 
>> return type.
>>
>>
>> David Gardner schrieb:
>>> Never worked with Brio, but have you defined a data-type in pgsql
> that
>>> your function would be returning? Something like:
>>>
>>> CREATE TYPE "myType" AS
>>> ("name" text,
>>> "start" timestamp without time zone,
>>> "end" timestamp without time zone,
>>> error text);
>>> ALTER TYPE "myType" OWNER TO "me";
>>>
>>> then the function definition would start off like:
>>> CREATE OR REPLACE FUNCTION "getData"()
>>> RETURNS SETOF "myType" AS
>>> ..... 
>>
>>
>> ---------------------------(end of
> broadcast)---------------------------
>> TIP 7: You can help support the PostgreSQL project by donating at
>>
>>                 http://www.postgresql.org/about/donate
>>
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
> 


In response to

pgsql-odbc by date

Next:From: Tom LaneDate: 2007-04-06 17:27:25
Subject: Re: Brio returning data through stored procedures.
Previous:From: Doug ThomDate: 2007-04-06 16:31:40
Subject: Re: Brio returning data through stored procedures.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group