[SQL] aggregation of setof

2011-01-28 Thread Andreas Gaab 
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:[email protected] * 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
___

[SQL] something simple but I can't

2011-01-28 Thread John Fabiani 
Hi guys,
I trying to return a 0.00 from a function it there are no records found else 
return the amount.

create or replace function danmeans_getpayments(text)
returns numeric as 
$BODY$
declare
  invoice_num ALIAS FOR $1;
  _paidamt numeric;

BEGIN
  select sum(aropen_paid) into _paidamt FROM public.aropen where 
aropen_applyto is not null and (aropen_applyto = $1) ;

IF (FOUND) THEN
  RETURN _paidamt ;
END IF;

  RETURN 0.00 ;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100

But all I get is either a blank or the amount paid.  What am I doing wrong???
Johnf

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] something simple but I can't

2011-01-28 Thread Jasen Betts 
On 2011-01-29, John Fabiani  wrote:
> Hi guys,
> I trying to return a 0.00 from a function it there are no records found else 
> return the amount.


>   select sum(aropen_paid) into _paidamt FROM public.aropen where 
> aropen_applyto is not null and (aropen_applyto = $1) ;
>
> IF (FOUND) THEN
>   RETURN _paidamt ;
>   END IF;
>   
>   RETURN 0.00 ;


> But all I get is either a blank or the amount paid.  What am I doing wrong???
> Johnf

 how many rows does the query return when no rows match the where?
 It returns 1  that looks like ( NULL ).
 it return 1 row, which is more than zero thus FOUND is TRUE.

you can fix your function  by changing the IF to

 IF _paidamt IS NOT NULL 


but if you change the sum to 

  coalesce(sum(aropen_paid),0.00)
  
you can do the task more simply like this:

 create or replace function danmeans_getpayments(text)
 returns numeric as 
 $BODY$
   select coalesce(sum(aropen_paid),0.00) FROM public.aropen where 
  aropen_applyto is not null and (aropen_applyto = $1) ;
 $BODY$
   LANGUAGE 'sql' ;

-- 
⚂⚃ 100% natural

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql