Обсуждение:
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
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@wp.pl>: > 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
On Friday 25 September 2009 9:52:09 am Pavel Stehule wrote: > 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 I may be misinterpreting the docs section below, but I beg to differ. http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Adrian Klaver aklaver@comcast.net
Hello 2009/9/26 Adrian Klaver <aklaver@comcast.net>: > On Friday 25 September 2009 9:52:09 am Pavel Stehule wrote: >> 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 > > I may be misinterpreting the docs section below, but I beg to differ. > > http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > the sql for EXECUTE statement should be dynamic, but the structure of result have to be stable. Pavel > > > > -- > Adrian Klaver > aklaver@comcast.net >
On Saturday 26 September 2009 1:10:20 pm Pavel Stehule wrote: > Hello > > 2009/9/26 Adrian Klaver <aklaver@comcast.net>: > > On Friday 25 September 2009 9:52:09 am Pavel Stehule wrote: > >> 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 > > > > I may be misinterpreting the docs section below, but I beg to differ. > > > > http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PL > >PGSQL-STATEMENTS-EXECUTING-DYN > > the sql for EXECUTE statement should be dynamic, but the structure of > result have to be stable. > > Pavel > RECORD type, RETURN RECORD ? 38.3.4. Record Types name RECORD; Record variables are similar to row-type variables, but they have no predefined structure. They take on the actual row structure of the row they are assigned during a SELECT or FOR command. The substructure of a record variable can change each time it is assigned to. A consequence of this is that until a record variable is first assigned to, it has no substructure, and any attempt to access a field in it will draw a run-time error. Note that RECORD is not a true data type, only a placeholder. One should also realize that when a PL/pgSQL function is declared to return type record, this is not quite the same concept as a record variable, even though such a function might use a record variable to hold its result. In both cases the actual row structure is unknown when the function is written, but for a function returning record the actual structure is determined when the calling query is parsed, whereas a record variable can change its row structure on-the-fly. -- Adrian Klaver aklaver@comcast.net