Thread: Brio returning data through stored procedures.
Hello, all,
I am wondering if anybody knows how to return data to Brio through ODBC from a function. If you are (or were) an Oracle user you may be familiar with defining a ref cursor type and including that as an OUT parameter. Brio then picks it up through ODBC and collects the results.
When I tried this in many different ways in Postgres the best I could get returned was either “<unnamed portal x>” where x increments for the number of times I tried it or what I passed in when I used the method that allows me to have the calling procedure name the cursor. But no data.
If anybody has run into this challenge I would love to hear if you were or were not successful. I’d really like to hear you were successful, of course, but if there is no way to do it then there’s no point me wasting any more time with it.
Thank you,
-Doug
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
.....
Doug Thom wrote:
>
> Hello, all,
>
> I am wondering if anybody knows how to return data to Brio through
> ODBC from a function. If you are (or were) an Oracle user you may be
> familiar with defining a ref cursor type and including that as an OUT
> parameter. Brio then picks it up through ODBC and collects the results.
>
> When I tried this in many different ways in Postgres the best I could
> get returned was either “<unnamed portal x>” where x increments for
> the number of times I tried it or what I passed in when I used the
> method that allows me to have the calling procedure name the cursor.
> But no data.
>
> If anybody has run into this challenge I would love to hear if you
> were or were not successful. I’d really like to hear you were
> successful, of course, but if there is no way to do it then there’s no
> point me wasting any more time with it.
>
> Thank you,
>
> -Doug
>
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
> .....
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.on@gmx.net]
Sent: Friday, April 06, 2007 9:50 AM
To: pgsql-odbc@postgresql.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
> .....
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.on@gmx.net]
> Sent: Friday, April 06, 2007 9:50 AM
> To: pgsql-odbc@postgresql.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
>
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.gardner@yucaipaco.com]
Sent: Friday, April 06, 2007 12:16 PM
To: Doug Thom; pgsql-odbc@postgresql.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.on@gmx.net]
> Sent: Friday, April 06, 2007 9:50 AM
> To: pgsql-odbc@postgresql.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
>
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.gardner@yucaipaco.com] > Sent: Friday, April 06, 2007 12:16 PM > To: Doug Thom; pgsql-odbc@postgresql.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.on@gmx.net] >> Sent: Friday, April 06, 2007 9:50 AM >> To: pgsql-odbc@postgresql.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 >
"Doug Thom" <dthom@iagr.net> writes:
> 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
I think you're looking for OPEN cursor FOR EXECUTE in plpgsql.
See the "cursor operations" part of the plpgsql manual.
regards, tom lane
Ah, sorry, right. I did that. Real code was correct, pseudo code was
bad. :)
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, April 06, 2007 1:27 PM
To: Doug Thom
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Brio returning data through stored procedures.
"Doug Thom" <dthom@iagr.net> writes:
> 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
I think you're looking for OPEN cursor FOR EXECUTE in plpgsql.
See the "cursor operations" part of the plpgsql manual.
regards, tom lane