dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Hi Greg,

Nothing really exotic about this. Simply create your function, and call it
like any other function from an SQL query.

Here is a simple function which returns orgunits with lower case names with
their UIDs

CREATE FUNCTION orgunit_to_lower(integer) RETURNS TABLE(name character
varying(160), uid character varying(11))
    AS $$ SELECT lower(name), uid from organisationunit
    LIMIT $1; $$
    LANGUAGE SQL;


This function can be used inside of another SQL query like

SELECT * FROM orgunit_to_lower(100)
WHERE name ~('^g');

Against the Demo database, you get something like this..

"gerehun chc";"TSyzvBiovKh"
"golu mchp";"azRICFoILuh"
"gelehun mchp";"FZxJ0KST9jn"
"gboyama chc";"k1Y0oNqPlmy"
"ganya mchp";"JttXgTlQAGE"


Of course, you can use PL/pgSQL if you need procedural code, or other
languages (like TCL, Java, Python, even our friend R)  if you prefer with
Postgresql extension.

You can see a use of this here in the DHIS2 documentation as well, which
creates  two new aggregate functions (median and skewness).
https://www.dhis2.org/doc/snapshot/en/developer/html/apas06.html

Best regards,
Jason






On Wed, Dec 10, 2014 at 6:49 AM, greg.rowles@xxxxxxxxx <
greg.rowles@xxxxxxxxx> wrote:

>  Hi Devs
>
>
> I've heard rumours of this functionality being discussed at your meeting
> in Jhb. Is there any further information?
>
>
> Kind regards,
>
> Greg
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>
>


-- 
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+46764147049