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

Re: Brio returning data through stored procedures.

From: "Doug Thom" <dthom(at)iagr(dot)net>
To: <pgsql-odbc(at)postgresql(dot)org>
Subject: Re: Brio returning data through stored procedures.
Date: 2007-04-06 16:31:40
Message-ID: A9D51A18173BD043BC843DAFB9CA48EC9A7A0D@VMAIL.iagr.net (view raw or flat)
Thread:
Lists: pgsql-odbc
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
> 



In response to

Responses

pgsql-odbc by date

Next:From: David GardnerDate: 2007-04-06 16:42:12
Subject: Re: Brio returning data through stored procedures.
Previous:From: David GardnerDate: 2007-04-06 16:16:01
Subject: Re: Brio returning data through stored procedures.

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