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