Обсуждение: return setof and Temp tables
I have looked around and found that you can use return setof in a function to return a result set, but can you use a temp table as the setof target? I build a temp table using various select statements and then try to return the result as a recordset. I get an error: type t1 does not exist.
If this is not possible, is there some alternative way to do this? I am trying to translate what was done in a ms sql database.
Justin
Justin B. Kay wrote: > If this is not possible, is there some alternative way to do this? I am > trying to translate what was done in a ms sql database. I have done it by returning a ref cursor to the temp table. I believe there are some ref cursor examples in the docs, if you can't find any let me know and I can send you a example. -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com
2006/2/17, Justin B. Kay <jkay@2pattersons.com>:
Please, send the sample. I test "Create temp table AS SELECT * FROM <setof function>" and works on 8.0.6
--
William Leite Araújo
I have looked around and found that you can use return setof in a function to return a result set, but can you use a temp table as the setof target?
Yes, you can ( PostgreSQL 8.0.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13))!
I build a temp table using various select statements and then try to return the result as a recordset. I get an error: type t1 does not exist.
Please, send the sample. I test "Create temp table AS SELECT * FROM <setof function>" and works on 8.0.6
--
William Leite Araújo
On Friday 17 February 2006 00:13, Justin B. Kay wrote:
> I have looked around and found that you can use return setof in a
> function to return a result set, but can you use a temp table as the
> setof target? I build a temp table using various select statements and
> then try to return the result as a recordset. I get an error: type t1
> does not exist.
> If this is not possible, is there some alternative way to do this? I am
> trying to translate what was done in a ms sql database.
>
If I were doing this, I would create a seperate permanent type that's
structure matched that of the temp table, or use out parameters to mimic it.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL