Hi,
I'm coming from Sybase ASE and Transact SQL.
Apart from long time desire to see true stored procedures in pl/pgsql,
with integer status and many arbitrary resultsets, I have one specific question:
is it possible to rename columns in rowset returned from function declared
as "returns table(...)" ?
As far as I know, the column names are always the same as in table(...)
declaration, even if 'AS "name"' is used in internal query.
I work with billing system in telecommunication company.
We have a system to publish business events about various changes to
MOM middleware (Websphere MQ).
There are triggers in various tables, and on changes, they insert records
into special table EVENTS:
ID identity,
EVENT numeric(8,0),
STATUS char(1), -- R - new, S - sent
<other parameters...>
There is a stored procedure, called periodically by external application,
which hits this table for STATUS=R (new) records, and, depending on EVENT field,
select varius data from EVENTS and other tables and returns rowset which
is transformed by application into XML message.
To make it simple, names and values of XML elements are encoded
as column names and column values, using "column name" = value,
which is Sybase equivalent of AS "column name".
The whole logic is in this procedure and calling application is extremely
simple - it was basically unmodified (except minor bug fixing) by almost 10 years.
Of course, rowsets returned for different EVENT values are different - various
column numbers and value types, but this is perfectly OK in TSQL.
I'd like to do the same in PostgreSQL, but:
- resulting rowset must be fixed and declared in 'returns table(...)' declaration.
I can declare all fileds "text" and convert them if needed. Ugly, butit should work.
- Number of columns in "table()" must be the highest needed and redundant columns
may be empty. Even more ugly, but it should work.
Is it possible to rename columns in returned rowset ?
If not, perhaps I need to return 2 rowsets - the first one with
element names, and the 2nd one with values.
Thanks in advance,
Jerzy
----------------------------------------------------
Rok szkolny 2009/2010.
Zobacz co cię czeka:
http://klik.wp.pl/?adr=http://corto.www.wp.pl/as/rokszkolny09-10.html&sid=869
