Thread: user defined XML aggregate not working as (i think) it should
Greetings! I have an xml aggregate function that always returns 'ERROR: invalid XML content'. However an identical function that uses text produces valid xml with the same inputs. create schema kml; CREATE FUNCTION kml.precon(text, text) RETURNS text AS $$ select $1||$2 $$ LANGUAGE sql; CREATE FUNCTION kml.precon(xml, xml) RETURNS xml AS $$ select xmlconcat($1,$2) $$ LANGUAGE sql; CREATE AGGREGATE kml.documentagg(text) ( SFUNC=kml.precon, STYPE=text, INITCOND=''); CREATE AGGREGATE kml.documentagg(xml) (SFUNC=kml.precon, STYPE=xml, INITCOND=''); with one as ( select '<Placemark><name>892033</name><description>MRIL-JULY-2012_RA 265/5-410</description><Point><coordinates>-76.801342826571982,17.98077767785141</coordinates></Point></Placemark>' k union select '<Placemark><name>900527</name><description>MRIL-JULY-2012_RA 265/5-810 </description><Point><coordinates>-76.803804151610237,17.98304310341128</coordinates></Point></Placemark>' union select '<Placemark><name>920661</name><description>MRIL-JULY-2012_RA 001/6-310</description><Point><coordinates>-77.886619999671524,18.441619925883373</coordinates></Point></Placemark>' ) select k from one ; /* returns 3 rows of text. expected*/ select k::xml from one ; /* returns 3 rows of xml. expected*/ select kml.documentagg(k) from one; /* returns one row text */ select kml.documentagg(k)::xml from one; /* returns one row xml */ select kml.documentagg(k::xml) from one ; /* ********** Error ********** ERROR: invalid XML content SQL state: 2200N */ Given that the text agg can be cast to xml without any errors I'm at a loss as to why the xml agg is failing. Regards, Rhys
"Rhys A.D. Stewart" <rhys.stewart@gmail.com> writes: > I have an xml aggregate function that always returns 'ERROR: invalid > XML content'. However an identical function that uses text produces > valid xml with the same inputs. I believe the reason this doesn't work is that the aggregate's initial value is faulty: it's supposed to be an XML value and it isn't. regression=# select ''::xml; ERROR: invalid XML content LINE 1: select ''::xml; ^ It's unfortunate that the CREATE AGGREGATE command doesn't complain about that --- maybe we could improve it. As far as making a working aggregate goes, I'd try leaving off the initcond clause (so that the initial value is NULL) and then marking the transition function STRICT. (plays around with xmlconcat on nulls ...) hm, maybe you'd not even need the STRICT marking, though personally I'd suggest it. regards, tom lane
Thanks Tom, That was indeed the issue. Regards, Rhys On Wed, Oct 3, 2012 at 8:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Rhys A.D. Stewart" <rhys.stewart@gmail.com> writes: >> I have an xml aggregate function that always returns 'ERROR: invalid >> XML content'. However an identical function that uses text produces >> valid xml with the same inputs. > > I believe the reason this doesn't work is that the aggregate's initial > value is faulty: it's supposed to be an XML value and it isn't. > > regression=# select ''::xml; > ERROR: invalid XML content > LINE 1: select ''::xml; > ^ > > It's unfortunate that the CREATE AGGREGATE command doesn't complain > about that --- maybe we could improve it. > > As far as making a working aggregate goes, I'd try leaving off the > initcond clause (so that the initial value is NULL) and then marking > the transition function STRICT. (plays around with xmlconcat on > nulls ...) hm, maybe you'd not even need the STRICT marking, though > personally I'd suggest it. > > regards, tom lane