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.bojo...@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.g...@scanlab.de<mailto:a.g...@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.g...@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
