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