pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedaggregation of setof
6+ messages / 4 participants
[nested] [flat]
* aggregation of setof
@ 2011-01-28 11:42 Andreas Gaab <A.Gaab@scanlab.de>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas Gaab @ 2011-01-28 11:42 UTC (permalink / raw)
To: pgsql-sql
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<http://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
___________________________________________________________________________
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: aggregation of setof
@ 2011-01-31 08:52 Andreas Gaab <A.Gaab@scanlab.de>
parent: Andreas Gaab <A.Gaab@scanlab.de>
0 siblings, 2 replies; 6+ messages in thread
From: Andreas Gaab @ 2011-01-31 08:52 UTC (permalink / raw)
To: pgsql-sql
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<mailto: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<http://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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: aggregation of setof
@ 2011-01-31 09:23 Pavel Stehule <pavel.stehule@gmail.com>
parent: Andreas Gaab <A.Gaab@scanlab.de>
1 sibling, 1 reply; 6+ messages in thread
From: Pavel Stehule @ 2011-01-31 09:23 UTC (permalink / raw)
To: Andreas Gaab <A.Gaab@scanlab.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: aggregation of setof
@ 2011-01-31 10:22 Andreas Gaab <A.Gaab@scanlab.de>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas Gaab @ 2011-01-31 10:22 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: aggregation of setof
@ 2011-01-31 15:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andreas Gaab <A.Gaab@scanlab.de>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2011-01-31 15:32 UTC (permalink / raw)
To: Andreas Gaab <A.Gaab@scanlab.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: aggregation of setof
@ 2011-01-31 15:37 Viktor Bojović <viktor.bojovic@gmail.com>
parent: Andreas Gaab <A.Gaab@scanlab.de>
1 sibling, 0 replies; 6+ messages in thread
From: Viktor Bojović @ 2011-01-31 15:37 UTC (permalink / raw)
To: Andreas Gaab <A.Gaab@scanlab.de>; +Cc: pgsql-sql
maybe custom types can be helpful. i use them when i have to many variables
or arrays.
On Mon, Jan 31, 2011 at 9:52 AM, Andreas Gaab <A.Gaab@scanlab.de> wrote:
> 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 <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
>
--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-01-31 15:37 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-28 11:42 aggregation of setof Andreas Gaab <A.Gaab@scanlab.de>
2011-01-31 08:52 ` Andreas Gaab <A.Gaab@scanlab.de>
2011-01-31 09:23 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-01-31 10:22 ` Andreas Gaab <A.Gaab@scanlab.de>
2011-01-31 15:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-01-31 15:37 ` Viktor Bojović <viktor.bojovic@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox