Thread: aggregation of setof

<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> 

Hello

use a array constructor instead

SELECT ARRAY(SELECT ...)

Regards

Pavel Stehule

2011/1/31 Andreas Gaab <A.Gaab@scanlab.de>:
> Functions apparently cannot take setof arguments.
>
>
>
> Postgres 8.4:
>
>
>
> CREATE OR REPLACE FUNCTION testtable(IN setof anyarray)
>
>   RETURNS anyarray AS
>
> $BODY$
>
> SELECT $1 LIMIT 1;
>
> $BODY$
>
>   LANGUAGE 'sql' STABLE;
>
>
>
> à
>
> ERROR:  functions cannot accept set arguments
>
>
>
>
>
>
>
> Von: Viktor Bojović [mailto:viktor.bojovic@gmail.com]
> Gesendet: Samstag, 29. Januar 2011 09:28
> An: Andreas Gaab
> Betreff: Re: [SQL] aggregation of setof
>
>
>
> i have never used that type but maybe you can try this;
>
> -create function which returns text[], and takse setof text as argument (if
> possible)
>
> -reach every text[] in set of text[] using array index
>
> -return values using "return next" for each text in text[] which is in set
> of text[]
>
>
>
> On Fri, Jan 28, 2011 at 12:42 PM, Andreas Gaab <A.Gaab@scanlab.de> wrote:
>
> Hi all,
>
>
>
> I would like to write a query, which aggregates the results of
> regexp_matches(). The problem is that regexp_matches returnes setof text[]
> as documented even if I discard the global flag
> (http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP
> ). Thus resulting in an error when I try to aggregate the result:
>
>
>
> “
>
> SELECT array_accum(
>
> regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)')
>
> )
>
> -------------------------------
>
> ERROR:  set-valued function called in context that cannot accept a set
>
> ********** Fehler **********
>
> ERROR: set-valued function called in context that cannot accept a set
>
> SQL Status:0A000
>
> “
>
>
>
> Can I convert a ‚setof text[]‘ to a ‚text[]‘?
>
>
>
> Alternatively I could use a sub-select, but I am curious if there are other
> solutions around.
>
>
>
> Regards,
>
> Andreas
>
>
>
> ___________________________________________________________________________
>
>
>
> SCANLAB AG
>
> Dr. Andreas Simon Gaab
>
> Entwicklung • R & D
>
>
>
> Siemensstr. 2a • 82178 Puchheim • Germany
>
> Tel. +49 (89) 800 746-513 • Fax +49 (89) 800 746-199
>
> mailto:a.gaab@scanlab.de • www.scanlab.de
>
>
>
> Amtsgericht München: HRB 124707 • USt-IdNr.: DE 129 456 351
>
> Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
>
> Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
>
> ___________________________________________________________________________
>
>
>
>
> --
> ---------------------------------------
> Viktor Bojović
> ---------------------------------------
> Wherever I go, Murphy goes with me

Hi,

Could not the function regexp_matches(text, text) be defined to only return a text[] not a setof text[]?

Because that is, what it actually does, or? For every input, it returns only one output row. The function
regexp_matches(text,text,text)in contrast really can return multiple rows while only receiving one row.
 


Regards,
Andreas


-----Ursprüngliche Nachricht-----
Von: Pavel Stehule [mailto:pavel.stehule@gmail.com] 
Gesendet: Montag, 31. Januar 2011 10:24
An: Andreas Gaab
Cc: pgsql-sql@postgresql.org
Betreff: Re: [SQL] aggregation of setof

Hello

use a array constructor instead

SELECT ARRAY(SELECT ...)

Regards

Pavel Stehule

2011/1/31 Andreas Gaab <A.Gaab@scanlab.de>:
> Functions apparently cannot take setof arguments.
>
>
>
> Postgres 8.4:
>
>
>
> CREATE OR REPLACE FUNCTION testtable(IN setof anyarray)
>
>   RETURNS anyarray AS
>
> $BODY$
>
> SELECT $1 LIMIT 1;
>
> $BODY$
>
>   LANGUAGE 'sql' STABLE;
>
>
>
> à
>
> ERROR:  functions cannot accept set arguments
>
>
>
>
>
>
>
> Von: Viktor Bojović [mailto:viktor.bojovic@gmail.com]
> Gesendet: Samstag, 29. Januar 2011 09:28
> An: Andreas Gaab
> Betreff: Re: [SQL] aggregation of setof
>
>
>
> i have never used that type but maybe you can try this;
>
> -create function which returns text[], and takse setof text as argument (if
> possible)
>
> -reach every text[] in set of text[] using array index
>
> -return values using "return next" for each text in text[] which is in set
> of text[]
>
>
>
> On Fri, Jan 28, 2011 at 12:42 PM, Andreas Gaab <A.Gaab@scanlab.de> wrote:
>
> Hi all,
>
>
>
> I would like to write a query, which aggregates the results of
> regexp_matches(). The problem is that regexp_matches returnes setof text[]
> as documented even if I discard the global flag
> (http://www.postgresql.org/docs/8.4/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP
> ). Thus resulting in an error when I try to aggregate the result:
>
>
>
> “
>
> SELECT array_accum(
>
> regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)')
>
> )
>
> -------------------------------
>
> ERROR:  set-valued function called in context that cannot accept a set
>
> ********** Fehler **********
>
> ERROR: set-valued function called in context that cannot accept a set
>
> SQL Status:0A000
>
> “
>
>
>
> Can I convert a ‚setof text[]‘ to a ‚text[]‘?
>
>
>
> Alternatively I could use a sub-select, but I am curious if there are other
> solutions around.
>
>
>
> Regards,
>
> Andreas
>
>
>
> ___________________________________________________________________________
>
>
>
> SCANLAB AG
>
> Dr. Andreas Simon Gaab
>
> Entwicklung • R & D
>
>
>
> Siemensstr. 2a • 82178 Puchheim • Germany
>
> Tel. +49 (89) 800 746-513 • Fax +49 (89) 800 746-199
>
> mailto:a.gaab@scanlab.de • www.scanlab.de
>
>
>
> Amtsgericht München: HRB 124707 • USt-IdNr.: DE 129 456 351
>
> Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
>
> Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
>
> ___________________________________________________________________________
>
>
>
>
> --
> ---------------------------------------
> Viktor Bojović
> ---------------------------------------
> Wherever I go, Murphy goes with me

Andreas Gaab <A.Gaab@scanlab.de> writes:
> Could not the function regexp_matches(text, text) be defined to only return a text[] not a setof text[]?

It'd be pretty hard to change at this point, because of
backwards-compatibility considerations, and because the two functions
share a single implementation.
        regards, tom lane

maybe custom types can be helpful. i use them when i have to many variables or arrays.




--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me