aggregation of setof - Mailing list pgsql-sql

<div class="WordSection1"><p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif"">Hiall,</span><p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif""> </span><pclass="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif"">Iwould like to write a query, which aggregates the results of regexp_matches(). The problem is that
regexp_matchesreturnes setof text[] as documented even if I discard the global flag (<a
href="http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP">http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP</a>
).Thus resulting in an error when I try to aggregate the result:</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">“</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">SELECT array_accum( </span><p class="MsoNormal"><span
lang="EN-US"style="font-size:11.0pt;font-family:"Times New Roman","serif"">regexp_matches('foobarbequebazilbarfbonk',
'(b[^b]+)(b[^b]+)')</span><pclass="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif"">)</span><pclass="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif"">-------------------------------</span><pclass="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">ERROR:  set-valued function called in context that cannot
accepta set</span><p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif"">**********Fehler **********</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">ERROR: set-valued function called in context that cannot
accepta set</span><p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Times New
Roman","serif"">SQLStatus:0A000</span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">“</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"TimesNew Roman","serif""> </span><p class="MsoNormal"><span lang="EN-US"
style="font-size:11.0pt;font-family:"TimesNew Roman","serif"">Can I convert a ‚setof text[]‘ to a ‚text[]‘?</span><p
class="MsoNormal"><spanlang="EN-US" style="font-size:11.0pt;font-family:"Times New Roman","serif""> </span><p
class="MsoNormal"><spanlang="EN-US" style="font-size:11.0pt;font-family:"Times New Roman","serif"">Alternatively I
coulduse a sub-select, but I am curious if there are other solutions around.</span><p class="MsoNormal"><span
lang="EN-US"style="font-size:11.0pt;font-family:"Times New Roman","serif""> </span><p class="MsoNormal"><span
lang="EN-US"style="font-size:11.0pt;font-family:"Times New Roman","serif"">Regards,</span><p class="MsoNormal"><span
lang="EN-US"style="font-size:11.0pt;font-family:"Times New Roman","serif"">Andreas</span><p class="MsoNormal"><span
lang="EN-US"style="font-size:11.0pt;font-family:"Times New Roman","serif""> </span><p class="MsoNormal"><span
style="font-size:10.0pt">___________________________________________________________________________</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt"> </span><p class="MsoNormal"><span
style="font-size:10.0pt">SCANLAB AG</span><pclass="MsoNormal"><span
style="font-size:10.0pt">Dr. Andreas Simon Gaab</span><pclass="MsoNormal"><span style="font-size:10.0pt">Entwicklung •
R & D</span><pclass="MsoNormal"><span style="font-size:10.0pt"> </span><p class="MsoNormal"><span
style="font-size:10.0pt">Siemensstr. 2a• 82178 Puchheim • Germany</span><p class="MsoNormal"><span
style="font-size:10.0pt">Tel. +49 (89) 800 746-513• Fax +49 (89) 800 746-199</span><p class="MsoNormal"><span
style="font-size:10.0pt"><ahref="mailto:a.gaab@scanlab.de">mailto:a.gaab@scanlab.de</a> • <a
href="http://www.scanlab.de">www.scanlab.de</a></span><pclass="MsoNormal"><span style="font-size:10.0pt"> </span><p
class="MsoNormal"><spanstyle="font-size:10.0pt">Amtsgericht München: HRB 124707 • USt-IdNr.: DE 129 456 351</span><p
class="MsoNormal"><spanstyle="font-size:10.0pt">Vorstand: Georg Hofner (Sprecher), Christian Huttenloher,
Norbert Petschik</span><pclass="MsoNormal"><span style="font-size:10.0pt">Aufsichtsrat (Vorsitz):
Dr. Hans J. Langer</span><pclass="MsoNormal"><span
style="font-size:10.0pt">___________________________________________________________________________</span><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif""></span><pclass="MsoNormal"> </div>