From: | Andreas Gaab <A(dot)Gaab(at)scanlab(dot)de> |
---|---|
To: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: aggregation of setof |
Date: | 2011-01-31 08:52:23 |
Message-ID: | 48DA836F3865C54B8FBF424A3B775AF6671A6838F8@Exchange-Server |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)bojovic(at)gmail(dot)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(dot)Gaab(at)scanlab(dot)de<mailto:A(dot)Gaab(at)scanlab(dot)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(dot)gaab(at)scanlab(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2011-01-31 09:23:56 | Re: aggregation of setof |
Previous Message | John Fabiani | 2011-01-29 15:14:22 | Re: something simple but I can't |