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