Function returning any (tuple) type
Hi, I wonder if anyone can help me find a solution for this problem. I'm
porting a database from MS SQL Server 2000 to PostgreSQL 8.0.1 (Windows).
We have an web interface that accesses the database, but doesn't have
direct access on any tables, only to execute a number of stored procedures.
Most of the stored procedures that return a large number of records
(mainly for reporting) store the results on transient tables created on
a separate database, naming the table as ResultsN where N is an
identifier that the stored procedure returns. All these created
transient tables include an indexed RowNumber column, with an
autoincremental value.
The returned identifier is later used by the web-front-end calling a
stored procedure which is the one I need help with. The stored procedure
returns a page of data for a requested transient table. Its code is
something like this:
CREATE PROCEDURE GetReportPage(@TableID int, @PageNo int) AS
EXECUTE('SELECT * FROM Results'+CONVERT(varchar, @TableID)+
'WHERE RowNumber >= '+CONVERT(varchar, @PageNo * 50)+
' AND RowNumber < '+CONVERT(varchar, (@PageNo+1) * 50))
The stored procedure is actually quite a lot more complex, because it
includes many other features, but I would like to achieve something
similar to this in PostgreSQL. This is very convenient because it allows
to see paged reports, sort them in different ways quickly, and even
export them later to CSV.
The main problem I see is that the funcitions in PostgreSQL seem to be
always bound to a particular result datatype. Is there a way to
circumvent this?
I've tried to solve this with arrays of text, but this is very
inconvenient and limiting. I've also tried with arrays of ROW and
RECORD, but it didn't work.
Are there any plugins or any way to allow functions to return arbitrary
row types? What about plans to include stored procedures in PGSQL in a
near future?
I hope I was clear enough, and the example in TransactSQL simple to
understand for non-MSSQL witty dbadmins. Please pg-wizards, lend me a
hand with this!
Cheers!
Ezequiel Tolnay
On 15.07.2005 08:51, Ezequiel Tolnay wrote:
The main problem I see is that the funcitions in PostgreSQL seem to be
always bound to a particular result datatype. Is there a way to
circumvent this?
I tried to find a solution for this as well some time ago. I don't
believe there is a practical way. It's only a guess, maybe this "strong
typing" helps the planer / optimizer in some way..
Hannes Dorbath wrote:
On 15.07.2005 08:51, Ezequiel Tolnay wrote:
The main problem I see is that the funcitions in PostgreSQL seem to be
always bound to a particular result datatype. Is there a way to
circumvent this?I tried to find a solution for this as well some time ago. I don't
believe there is a practical way. It's only a guess, maybe this "strong
typing" helps the planer / optimizer in some way..
If that's not what you want, you can always return text (structure it
how you like) or a cursor-reference.
--
Richard Huxton
Archonet Ltd
On Fri, Jul 15, 2005 at 04:51:04PM +1000, Ezequiel Tolnay wrote:
Hi, I wonder if anyone can help me find a solution for this problem. I'm
porting a database from MS SQL Server 2000 to PostgreSQL 8.0.1 (Windows).
<snip>
The main problem I see is that the funcitions in PostgreSQL seem to be
always bound to a particular result datatype. Is there a way to
circumvent this?
Two solutions I can think of:
- Return SETOF RECORD and specify the type on the actual query from the
client
- Return a cursor reference.
Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.