Search Postgresql Archives

How to find string intersection

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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;


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux