Hello this isn't possible now. All what you want are limited by SELECT statement in PostgreSQL. Simply PL knows only SELECT statement and structure of result have to be strongly static - because it is based on static execution plan - it is determined before query start. Statement CALL isn't implemented yet. Regards Pavel Stehule 2009/9/25 Jerzy Bialas <jbialas3@xxxxx>: > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
