Function parameter named classes contains 1..4 (can be more if this
simplifies solution) uppercase characters or digits.
It checks for those character presence in summak.klass column ( this column
type is CHAR(10) )
To solve this I created function below but this requires always 4 characters
to be passed even if actually single char check is required like
select paidinperiod('00110', date'1900-01-01', current_date, 'JJJJ')
and contains ugly POSITION / SUBSTRING lines.
How to change this so that classes parameter can be also single character
like
select paidinperiod('00110', date'1900-01-01', current_date, 'J')
and it returns same result?
classes parameter is passed as literal characters always. It is possible to
change function signature to pass classes as array
or in other way if this makes solution easier.
Also, can this function improved or simplified by any other way ?
Andrus.
CREATE OR REPLACE FUNCTION paidinperiod(personID text, paidfrom date, paidto
date, classes text )
RETURNS Numeric AS
$BODY$
DECLARE
result record;
begin
SELECT SUM(
CASE WHEN POSITION( SUBSTRING( classes FROM 1 FOR 1 ) IN summak.klass)!=0 OR
POSITION( SUBSTRING( classes FROM 2 FOR 1 ) IN summak.klass)!=0 OR
POSITION( SUBSTRING( classes FROM 3 FOR 1 ) IN summak.klass)!=0 OR
POSITION( SUBSTRING( classes FROM 4 FOR 1 ) IN summak.klass)!=0
THEN
CASE WHEN summak.tyyp='Tulu' THEN 1 ELSE -1 END
ELSE 0 END * arvestatud ) as res
into result
from VMAKS JOIN SUMMAV USING (vmnr) JOIN SUMMAK USING (kood)
where summav.isik=personID AND vmaks.maksekuup BETWEEN paidfrom AND paidto;
return coalesce(result.res,0);
END;
$BODY$ language plpgsql stable;
