Database functions

- test mode -


Daftar isi

add_checkdigit

Function call:
add_checkdigit (inval bigint) bigint
Schema:
reference
Description:
Given a number, return it with a check digit appended to the end of it.

addlookuprecord

Function call:
addlookuprecord (lookuptype character varying, cde character varying, name character varying, descr character varying) integer
Schema:
metadata

animals_affected_report

Function call:
animals_affected_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (ANIMALS_AFFECTED_REPORT(casechid,usrid,1)).*;

anyerrors

Function call:
anyerrors (result inputprocessresult) boolean
Schema:
metadata

app_checkpin

Function call:
app_checkpin (userphone character varying, userpin character varying) json
Schema:
metadata
Description:
App phone number validation system. Given a user phone number and pin, return matching user details.

app_get_form

Function call:
app_get_form (smsid integer, userid integer, OUT form_def json) json
Schema:
metadata
Description:
Generate JSON form definition for mobile App based on sms metadata

app_get_project_by_phone

Function call:
app_get_project_by_phone (userphone character varying, OUT project_def json) json
Schema:
metadata

app_get_project_grouped

Function call:
app_get_project_grouped (smsuserid integer, OUT project_def json) json
Schema:
metadata
Description:
Generate project JSON for mobile app (grouped version)

app_get_project

Function call:
app_get_project (smsuserid integer, OUT project_def json) json
Schema:
metadata
Description:
Generate project JSON for mobile app, based on user phone number

app_get_static

Function call:
app_get_static (static_id integer, userid integer, OUT static_data text) text
Schema:
metadata
Description:
Download App static data

app_startup

Function call:
app_startup (userphone character varying) boolean
Schema:
metadata
Description:
App phone number validation system. Given a user phone number, generate a random pin, store it in the user's record, and send a confirmation SMS message.

armor

Function call:
armor ( bytea) text
Schema:
backoffice

armor

Function call:
armor ( bytea, text[], text[]) text
Schema:
backoffice

boolop

Function call:
boolop ( integer[], query_int) boolean
Schema:
backoffice
Description:
boolean operation with array

businessrulehandler

Function call:
businessrulehandler (hookname hook, uid integer, callingfunc character varying, args character varying[], sms_msgid bigint) businessrulereturn
Schema:
backoffice
Description:
The function calls business rule handler functions which match the hook name and caller name input arguments. There can be 0, 1, or more business rule functions which match.

Business rule functions are assumed to take the userid as the first argument, followed by the arguments passed into this function in the args array, and to return a composite object consisting of a return code and a error string. If the business rule function succeeds, it returns a result code of zero, and an empty or null error string. If the business rule function encounters a non-fatal error, it returns a result code of zero, and a non-empty error string. In this case, further rule handlers can be called. If the business rule function has a fatal error, it returns a result code of 1, and a non-empty error string. In this case, any further business rule handlers are not called.

If an internal error occurs in a rule handler function, causing an exception to be thrown, this will be trapped and treated as if it was an error return.

This function returns 0 and an empty string to indicate success, or 1 and an error message to indicate failure. If multiple errors were detected, the error strings are concatenated with linefeed characters.

case_comments_report

Function call:
case_comments_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (CASE_COMMENTS_REPORT(casechid,usrid,1)).*;

caseimages_report

Function call:
caseimages_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (CASEIMAGES_REPORT(casechid,usrid,1)).*;

case_resolved_report

Function call:
case_resolved_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (CASE_RESOLVED_REPORT(casechid,usrid,1)).*;

check_d_codes

Function call:
check_d_codes (codes text[], sms_userid integer, OUT msg character varying) character varying
Schema:
sms

checklocationcontained

Function call:
checklocationcontained (parentid integer, locid integer) boolean
Schema:
reference

checklookupcode

Function call:
checklookupcode (lkuptypid integer, codeval character varying) integer
Schema:
metadata

cleanname

Function call:
cleanname (name character varying) character varying
Schema:
metadata

clinical_signs_report

Function call:
clinical_signs_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (CLINICAL_SIGNS_REPORT(casechid,usrid,1)).*;

columnintable

Function call:
columnintable (tblname character varying, colname character varying) boolean
Schema:
metadata

confirm_register_user

Function call:
confirm_register_user (userid integer, sms_pin character varying, sms_msgid bigint, OUT rslt integer, OUT msg character varying) record

Schema:
backoffice
Description:
This function confirms a user as registered by:
  1. finding their phone number,
  2. adding the number to the user record with a matching pin,
  3. before setting the pin to NULL.

connectby

Function call:
connectby ( text, text, text, text, integer) setof record
Schema:
backoffice

connectby

Function call:
connectby ( text, text, text, text, integer, text) setof record
Schema:
backoffice

connectby

Function call:
connectby ( text, text, text, text, text, integer) setof record
Schema:
backoffice

connectby

Function call:
connectby ( text, text, text, text, text, integer, text) setof record
Schema:
backoffice

createbasetype

Function call:
createbasetype (ourname character varying, descr character varying, internalname character varying) integer
Schema:
metadata

createdatatable

Function call:
createdatatable (tblid integer) boolean
Schema:
metadata

createdatatablerecord

Function call:
createdatatablerecord (tblnam character varying) integer
Schema:
metadata

createdatatablerecord

Function call:
createdatatablerecord (tblnam character varying, parent integer, parentlinkfield character varying) integer
Schema:
metadata

createdomaintype

Function call:
createdomaintype (ourname character varying, descr character varying, internalname character varying, checkcond character varying) integer
Schema:
metadata

create_lab_standards_file

Function call:
create_lab_standards_file (standard integer, labcode character varying) character varying
Schema:
reference

createlookuptable

Function call:
createlookuptable (ourname character varying, schema character varying, synonyms boolean) integer
Schema:
metadata

createlookuptypefortable

Function call:
createlookuptypefortable (lookuptype character varying, descr character varying, tblname character varying, lkupcol character varying) integer
Schema:
metadata

createlookuptype

Function call:
createlookuptype (ourname character varying, descr character varying) integer
Schema:
metadata

create_password

Function call:
create_password (passlength integer) text
Schema:
backoffice
Description:
Creates a random password of the specified length using the following characters only:

23456789ABCDEFGHJKMNPQRSTUVWXYZabcdefghjkmnpqrstuvwxyz

create_pin

Function call:
create_pin (pinlength integer, OUT pin text) text
Schema:
backoffice
Description:
Creates a random numeric PIN number of the specified lenght (default 6 digits), for use during user registration.

createtablefield

Function call:
createtablefield (tblid integer, fname character varying[], typid integer, keyf boolean, mand boolean, repeat boolean) boolean
Schema:
metadata

crosstab2

Function call:
crosstab2 ( text) setof tablefunc_crosstab_2
Schema:
backoffice

crosstab3

Function call:
crosstab3 ( text) setof tablefunc_crosstab_3
Schema:
backoffice

crosstab4

Function call:
crosstab4 ( text) setof tablefunc_crosstab_4
Schema:
backoffice

crosstab

Function call:
crosstab ( text, integer) setof record
Schema:
backoffice

crosstab

Function call:
crosstab ( text) setof record
Schema:
backoffice

crosstab

Function call:
crosstab ( text, text) setof record
Schema:
backoffice

crypt

Function call:
crypt ( text, text) text
Schema:
backoffice

custom_excel_import

Function call:
custom_excel_import (jid integer, mail_userid integer, mail_msgid bigint, sheet character varying) integer
Schema:
metadata
Description:
Parses custom lab spreadsheet data and inserts it into iSIKHNAS.

Called by parse_XM.

Parameters

  • JID: jobid from the import jobs list (currently only 1)
  • msgid: unique message ID assigned at receipt of the email

dearmor

Function call:
dearmor ( text) bytea
Schema:
backoffice

decrypt

Function call:
decrypt ( bytea, bytea, text) bytea
Schema:
backoffice

decrypt_iv

Function call:
decrypt_iv ( bytea, bytea, bytea, text) bytea
Schema:
backoffice

definitive_diagnosis_report

Function call:
definitive_diagnosis_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (DEFINITIVE DIAGNOSIS_REPORT(casechid,usrid,1)).*;

del_ah

Function call:
del_ah (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_ai

Function call:
del_ai (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_bb

Function call:
del_bb (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_bcs

Function call:
del_bcs (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_bh

Function call:
del_bh (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_cip

Function call:
del_cip (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_cul

Function call:
del_cul (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_dh

Function call:
del_dh (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_dnc

Function call:
del_dnc (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_dp

Function call:
del_dp (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_dsb

Function call:
del_dsb (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_dx

Function call:
del_dx (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

delete_email

Function call:
delete_email (bademail text, reason text) boolean
Schema:
backoffice
Description:
Deletes the specified email address, and sends an SMS alert to the user, and an email alert to their kabupaten coordinator alerting them of the fact and the reason

deletetablerecords

Function call:
deletetablerecords (tblname character varying, condition character varying) integer
Schema:
metadata

del_hk

Function call:
del_hk (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_hpt

Function call:
del_hpt (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_ib

Function call:
del_ib (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_image

Function call:
del_image (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_jh

Function call:
del_jh (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_kgg

Function call:
del_kgg (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_kom

Function call:
del_kom (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_lab

Function call:
del_lab (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_lh

Function call:
del_lh (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_ltl

Function call:
del_ltl (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_ob

Function call:
del_ob (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_obi

Function call:
del_obi (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_pg

Function call:
del_pg (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_p

Function call:
del_p (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_pkb

Function call:
del_pkb (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_pk

Function call:
del_pk (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_pkl

Function call:
del_pkl (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_pl

Function call:
del_pl (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_pnc

Function call:
del_pnc (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_pneg

Function call:
del_pneg (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_pop

Function call:
del_pop (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_prp

Function call:
del_prp (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_ps

Function call:
del_ps (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_q

Function call:
del_q (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_r

Function call:
del_r (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_rp

Function call:
del_rp (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_rvak

Function call:
del_rvak (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_sbh

Function call:
del_sbh (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_sk

Function call:
del_sk (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_sko

Function call:
del_sko (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_skp

Function call:
del_skp (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_sksr

Function call:
del_sksr (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_slab

Function call:
del_slab (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_slap

Function call:
del_slap (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_snc

Function call:
del_snc (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_sp

Function call:
del_sp (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_spk

Function call:
del_spk (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_srab

Function call:
del_srab (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_ssb

Function call:
del_ssb (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_sur

Function call:
del_sur (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_tk

Function call:
del_tk (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_tl

Function call:
del_tl (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_uc

Function call:
del_uc (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_u

Function call:
del_u (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_ui

Function call:
del_ui (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_vak

Function call:
del_vak (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

del_z

Function call:
del_z (sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

differential_diagnosis_report

Function call:
differential_diagnosis_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (DIFFERENTIAL_DIAGNOSIS_REPORT(casechid,usrid,1)).*;

digest

Function call:
digest ( bytea, text) bytea
Schema:
backoffice

digest

Function call:
digest ( text, text) bytea
Schema:
backoffice

encrypt

Function call:
encrypt ( bytea, bytea, text) bytea
Schema:
backoffice

encrypt_iv

Function call:
encrypt_iv ( bytea, bytea, bytea, text) bytea
Schema:
backoffice

excel_serial_to_date

Function call:
excel_serial_to_date (indate character varying) date
Schema:
backoffice
Description:
Converts an Excel serial date value (as a sting input) to a PostgreSQL date value. Detects if the string is actually a text version date format.

find_function

Function call:
find_function (functionname text) setof text
Schema:
backoffice

format_phone

Function call:
format_phone (in_phone character varying, OUT result character varying) character varying
Schema:
reference
Description:
Given an international phone number, this function returns a local number.

SELECT FORMAT_PHONE(phone) FROM anytable;

phone is a phone number assumed to be in international format (that is, beginning with 62); however, checks are not done to ensure this is so. The first two characters of the phone number are replaced with 0.

Note: probably better to replace this function (where used) with local_phone, which DOES check whether have international format. (At this stage, identical copies of local_phone are stored in reference and backoffice.

general_disease_report

Function call:
general_disease_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (GENERAL_DISEASE_REPORT(casechid,usrid,1)).*;

generate_checkdigit

Function call:
generate_checkdigit (inval bigint) bigint
Schema:
reference
Description:
Given a number, return the checkdigit that should be appended to it.

generate_export_standard

Function call:
generate_export_standard (expid integer) integer
Schema:
reference

generatekeyselectcondition

Function call:
generatekeyselectcondition (flddata projfielddata[], keyfields integer[], outdata character varying[]) character varying
Schema:
metadata

generate_lab_standards

Function call:
generate_lab_standards (standard integer, labcode character varying) integer
Schema:
reference

gen_random_bytes

Function call:
gen_random_bytes ( integer) bytea
Schema:
backoffice

gen_salt

Function call:
gen_salt ( text, integer) text
Schema:
backoffice

gen_salt

Function call:
gen_salt ( text) text
Schema:
backoffice

get_age

Function call:
get_age (birthday date, userid integer) character varying
Schema:
backoffice

get_animal_id_by_centre

Function call:
get_animal_id_by_centre (bibcode character varying, eartagcode character varying, creator integer, infratypeid integer, species integer, dob date, gender integer, OUT animalid integer) integer
Schema:
backoffice

get_animal_id

Function call:
get_animal_id (ownerid integer, anident character varying, OUT animalid integer) integer
Schema:
backoffice
Description:
Returns the unique animal ID based on tag number and internal iSIKHNAS owner ID

get_animal_id

Function call:
get_animal_id (phone character varying, anident character varying, sms_userid integer, OUT animalid integer) integer
Schema:
backoffice

get_animal_types_name

Function call:
get_animal_types_name (antype_id integer, user_id integer, OUT animal_types_name character varying) character varying
Schema:
reference
Description:
This function returns the animal type in the viewer's language.

SELECT get_animal_types_name(animaltypeid, <<userid>>) FROM anytable;

anytable is any table that contains an animaltypeid field (for example, reference.animal_types (field name: id), or data.slaughtertotals (field name: animaltypeid).

getbasetypeoftype

Function call:
getbasetypeoftype (typeoid oid) character varying
Schema:
metadata

get_cell_ref

Function call:
get_cell_ref (ref character varying, rid integer, parentcell integer, rowcol character) character varying
Schema:
metadata

getcodelookupdata

Function call:
getcodelookupdata (lkuptypid integer) public.hstore
Schema:
metadata

getcodelookupquery

Function call:
getcodelookupquery (lkuptypid integer) character varying
Schema:
metadata

get_data_range

Function call:
get_data_range (trange character varying, rowcol character, OUT rng integer[]) integer[]
Schema:
metadata
Description:
Get the min and max non-empty row in an excel-like range

getdatatableid

Function call:
getdatatableid (tblnam character varying) integer
Schema:
metadata

getdaterange

Function call:
getdaterange (datetype integer, basedate date) daterange
Schema:
reference

getdaterangelimits

Function call:
getdaterangelimits (datetype integer, basedate date) date[]
Schema:
reference

get_differential

Function call:
get_differential (sms_caseid integer, sms_userid integer, lim integer, id integer, disease text, sx double precision) setof record
Schema:
reference

get_disease_name

Function call:
get_disease_name (d_code integer, user_id integer, OUT d_name character varying) character varying
Schema:
reference
Description:
The parameters are 1) a diseaseid, and 2) the userid. The function returns the disease name, in the language of the report viewer.

SELECT  get_disease_name(diseaseid, <<userid>>) FROM anytable;

get_disease_names

Function call:
get_disease_names (d_codes integer[], user_id integer, OUT d_names character varying) character varying
Schema:
reference
Description:
This function must be applied to an ARRAY. Its parameters are 1) an array of diseaseids, and 2) the userid. The function returns a comma-separated list of the corresponding disease names, in the language of the report viewer.

SELECT  get_disease_names(diseaseidarray, <<userid>>) FROM anytable;

If your query is not based on a table field that is already in array format, you can aggregate the data using a SELECT query with a GROUP BY clause, then convert the data into an array using ARRAY_AGG:

SELECT item1, get_disease_names(ARRAY_AGG(diseaseid)) FROM anytable GROUP BY item1;

get_drug_names

Function call:
get_drug_names (d_codes integer[], OUT d_names character varying) character varying
Schema:
reference
Description:
This function must be applied to an ARRAY. Its parameters are an array of drugids (e.g. for vaccines, this is 15). The function returns a comma-separated list of the corresponding vames. SELECT get_drug_names(drugidarray) FROM drugs;

If your query is not based on a table field that is already in array format, you can aggregate the data using a SELECT query with a GROUP BY clause, then convert the data into an array using ARRAY_AGG: SELECT item1, get_drug_names(ARRAY_AGG(drugid)) FROM anytable GROUP BY item1;

get_dup_anident

Function call:
get_dup_anident (ownerid character varying, anident character varying, userid integer, OUT dup_anident character varying) character varying
Schema:
backoffice

get_farmer_id

Function call:
get_farmer_id (phone bigint, sms_userid integer, OUT farmerid integer) integer
Schema:
backoffice

get_farmer_id

Function call:
get_farmer_id (phone character varying, sms_userid integer, OUT farmerid integer) integer
Schema:
backoffice

get_farmer_ids

Function call:
get_farmer_ids (phone character varying, sms_userid integer, OUT farmerid integer) setof integer
Schema:
backoffice

get_group_name

Function call:
get_group_name (groupid integer, user_id integer, OUT group_name character varying) character varying
Schema:
reference
Description:
This functions returns the human readable name of a group, given the groupid, returned in the language of the viewer of the report.

SELECT get_group_name(groupid, <<userid>>) 
FROM ANYTABLE;

If no value is provided for the language of the user of the report, the language of the subject of the report is used instead.

get_group_names

Function call:
get_group_names (groupids integer[], user_id integer, OUT group_names character varying) character varying
Schema:
reference
Description:
Given an array of groupids, this functions returns a human readable list formatted for HTML display of group names, returned in the language of the viewer of the report.

SELECT get_group_names(groupids, <<userid>>) 
FROM ANYTABLE;

If no value is provided for the language of the user of the report, the language of the subject of the report is used instead.

get_hostname

Function call:
get_hostname (OUT hostname text) text
Schema:
metadata
Description:
This function is not as smart as it might appear. It relies on the /etc/hostname file being set during the cloud-init process (or sometime thereafter).
This function will not work on a replica as it creates a temporary table to store the data during loading. [BM]

get_infra_id

Function call:
get_infra_id (infra_type character varying, infra_code character varying, sms_userid integer) integer
Schema:
reference

get_infra_id

Function call:
get_infra_id (infra_type integer, infra_code character varying, sms_userid integer) integer
Schema:
reference

get_infrastructure_name

Function call:
get_infrastructure_name (infra_id integer, OUT i_name character varying) character varying
Schema:
reference
Description:
Given the infrastructureid, the function returns the name of the infrastructure.

SELECT get_infrastructure_name(infraid) FROM anytable;

get_infrastructure_names

Function call:
get_infrastructure_names (infra_ids integer[], OUT i_names character varying) character varying
Schema:
reference
Description:
This function must be applied to an ARRAY of infrastructureids. The function returns a semicolon-separated list of the corresponding infrastructure names.

SELECT  get_infrastructure_names(infrastructureidarray) FROM anytable;

If your query is not based on a table field that is already in array format, you can aggregate the data using a SELECT query with a GROUP BY clause, then convert the data into an array using ARRAY_AGG:

SELECT item1, get_infrastructure_names(ARRAY_AGG(infrastructureid)) FROM anytable GROUP BY item1;

get_ivm

Function call:
get_ivm (start_date date, end_date date, OUT created_at timestamp with time zone, OUT district_code integer, OUT district_name character varying, OUT province_id integer, OUT epi_lab_number integer, OUT epi_lab_ref_number character varying, OUT epi_year character varying, OUT htype integer, OUT ntype integer, OUT id integer, OUT isolate_name character varying, OUT virology_lab_identifier character varying) setof record
Schema:
backoffice

get_lang

Function call:
get_lang (user_id integer) integer
Schema:
backoffice
Description:
Return a user's preferred language

get_location

Function call:
get_location (loccode character varying, OUT locationname character varying) character varying
Schema:
reference

get_location

Function call:
get_location (locid integer, OUT locationname character varying) character varying
Schema:
reference

get_location_id

Function call:
get_location_id (loccode character varying) integer
Schema:
reference

get_location_id

Function call:
get_location_id (loccode character varying, userid integer, force_village boolean) integer
Schema:
reference

get_locationlevel_name

Function call:
get_locationlevel_name (ll_code integer, user_id integer, OUT ll_name character varying) character varying
Schema:
reference
Description:
The parameters are 1) a locationlevel code, and 2) the userid. The function returns the location level name, in the language of the report viewer.

SELECT  get_locationlevel_name(locationlevelcode, <<userid>>) FROM anytable;

get_location_names

Function call:
get_location_names (loc_ids integer[], OUT i_names character varying) character varying
Schema:
reference
Description:
This function must be applied to an ARRAY of locationids. The function returns a semicolon-separated list of the corresponding location names.

SELECT  get_location_names(locationidarray) FROM anytable;

If your query is not based on a table field that is already in array format, you can aggregate the data using a SELECT query with a GROUP BY clause, then convert the data into an array using ARRAY_AGG:

SELECT item1, get_location_names(ARRAY_AGG(locationid)) FROM anytable GROUP BY item1;

get_location_names_rev

Function call:
get_location_names_rev (loc_ids integer[], OUT i_names character varying) character varying
Schema:
reference
Description:
This function must be applied to an ARRAY of locationids. The function returns a semicolon-separated list of the corresponding location names, with levels ordered from general to specific.

SELECT  get_location_names_rev(locationidarray) FROM anytable;

If your query is not based on a table field that is already in array format, you can aggregate the data using a SELECT query with a GROUP BY clause, then convert the data into an array using ARRAY_AGG:

SELECT item1, get_location_names_rev(ARRAY_AGG(locationid)) FROM anytable GROUP BY item1;

get_location_rev

Function call:
get_location_rev (loccode character varying, OUT locationname character varying) character varying
Schema:
reference

get_location_rev

Function call:
get_location_rev (locid integer, OUT locationname character varying) character varying
Schema:
reference

getlookuplistquery

Function call:
getlookuplistquery (lkuptypid integer, userid integer) character varying
Schema:
metadata

get_messagecode

Function call:
get_messagecode (msgid_code bigint, OUT mcode character varying) character varying
Schema:
reference
Description:
The parameter is a msgid. The function returns the messagecode.

SELECT  get_messagecode(msgid) FROM anytable;

get_messagecode_name

Function call:
get_messagecode_name (msgid_code bigint, user_id integer, OUT mt_name character varying) character varying
Schema:
reference
Description:
The parameters are 1) a msgid, and 2) the userid. The function returns the messagecode name, in the language of the report viewer.

SELECT  get_messagecode_name(msgid, <<userid>>) FROM anytable;

get_messagetypeid

Function call:
get_messagetypeid (msgid bigint, OUT mtypeid integer) integer
Schema:
reference
Description:
The parameter is a msgid. The function returns the messagetypeid.

SELECT  get_messagetypeid(msgid) FROM anytable;

get_month_name

Function call:
get_month_name (month_id integer, user_id integer, OUT month_name character varying) character varying
Schema:
reference
Description:
This function returns the name of the day of the week in the viewer's language. It can be used on a date field as shown:

SELECT get_month_name(EXTRACT(MONTH FROM reportdate)::integer, <<userid>>) FROM anytable;

get_owner_id

Function call:
get_owner_id (ownername character varying, owneraddress character varying, ownerphone phonenumber, ownerlocationid integer, creator integer, ownermsgid integer, OUT ownerid integer) integer
Schema:
backoffice

get_owner_id

Function call:
get_owner_id (ownername character varying, owneraddress character varying, ownerphone phonenumber, ownerlocationid integer, ownermsgid integer, OUT ownerid integer) integer
Schema:
backoffice
Description:
Stephen Kirby Created this for the use in the infolab spreadsheet import

get_palette_code

Function call:
get_palette_code (palette_id integer, OUT palette_code character varying) character varying
Schema:
reference
Description:
This function returns the R-readable code for the selected Colorbrewer palette, for use in charts.

SELECT GET_PALETTE_CODE(<<palette>>) FROM anytable;

<<palette>> is brewerpalettes.id, or is selected as a lookupone filter through the interface.

get_param

Function call:
get_param (pname character varying) character varying
Schema:
backoffice
Description:
Returns the specified parameter value from the parameters table. These parameters are intended for general system variables.

get_pdsr_hpai_village_status

Function call:
get_pdsr_hpai_village_status (desa integer, basedate timestamp without time zone, period integer) integer
Schema:
reference

get_pop

Function call:
get_pop (species integer, locationid integer, reportdate date, population integer) setof record
Schema:
data
Description:
Returns a table contining the most up to date population data for all villages for the specified species. Table fields are locationid, reportdate, population.

get_population

Function call:
get_population (sp integer, loc integer, OUT total integer, OUT createdon timestamp without time zone) record
Schema:
backoffice
Description:
Returns the most recent population data for the specified species and location. The function returns a record with two fields: total and createdon. Usage:

 select (get_population(22,67720)).total ;
 select * from get_population(22,67720);
 select createdon from get_population(22,6772);

getprojectfielddata

Function call:
getprojectfielddata (prjid integer) projfielddata[]
Schema:
metadata

get_purpose_name

Function call:
get_purpose_name (p_code integer, user_id integer, OUT p_name character varying) character varying
Schema:
reference
Description:
The parameters are 1) a purposeid, and 2) the userid. The function returns the purpose name, in the language of the report viewer.

SELECT  get_purpose_name(purposeid, <<userid>>) FROM anytable;

getrectodeleteid

Function call:
getrectodeleteid (tblnm character varying, flddata projfielddata[], keyfields integer[], outdata character varying[]) integer
Schema:
metadata

get_related_table

Function call:
get_related_table (_table text, a text, b text, c text, d text) setof record
Schema:
lab

getroottypeoftype

Function call:
getroottypeoftype (typeoid oid) oid
Schema:
metadata

get_sign_name

Function call:
get_sign_name (s_code integer, user_id integer, OUT s_name character varying) character varying
Schema:
reference
Description:
The parameters are 1) a signid, and 2) the userid. The function returns the sign name, in the language of the report viewer.

SELECT  get_sign_name(signid, <<userid>>) FROM anytable;

get_sign_names

Function call:
get_sign_names (signids integer[], user_id integer, OUT sign_names character varying) character varying
Schema:
backoffice
Description:
Returns a string containing a list of sign names corresponding to the sign codes in the passed array, in the viewer's language.

SELECT get_sign_names(signsidarray, <<userid>> FROM signreports;

get_sign_names

Function call:
get_sign_names (signids integer[], user_id integer, OUT sign_names character varying) character varying
Schema:
reference
Description:
Returns a string containing a list of sign names corresponding to the sign codes in the passed array, in the viewer's language.

SELECT get_sign_names(signsidarray, <<userid>> FROM signreports;

get_sign_names

Function call:
get_sign_names (signids integer[], user_id integer, OUT sign_names character varying) character varying
Schema:
sms
Description:
Returns a string containing a list of sign names corresponding to the sign codes in the passed array, in the viewer's language.

SELECT get_sign_names(signsidarray, <<userid>> FROM signreports;

get_singlecolour_code

Function call:
get_singlecolour_code (OUT singlecolour_code character varying) character varying
Schema:
reference
Description:
This function returns the 'R-readable' code for the iSIKHNAS standard colour, to be passed to R for use in charts that have a single colour. The colour is hard-wired into the function; currently, it is set as 'firebrick'.

SELECT GET_SINGLECOLOUR_CODE() FROM anytable;

get_sms_cname

Function call:
get_sms_cname (sms_id integer, user_id integer, OUT sms_cname character varying) character varying
Schema:
reference
Description:
Returns the code and name of the sms in the form code: name, based on the sms id and the user id to determine the correct language

SELECT get_sms_cname(smsid, <<userid>>) FROM anytable;

get_sms_cnames

Function call:
get_sms_cnames (sms_ids integer[], user_id integer, OUT sms_cnames character varying) character varying
Schema:
reference
Description:
This function must be applied to an ARRAY. It returns a comma-separated list (on separate lines) of the code and name of the sms in the form code: name, based on the sms id and the user id to determine the correct language

SELECT get_sms_cnames(smsidarray, <<userid>>) FROM anytable;

get_sms_format_description

Function call:
get_sms_format_description (sms_start_code character varying, lang integer, OUT sms_format character varying) character varying
Schema:
backoffice
Description:
Stub for real CUSUM to determine if the current period's level or reporting is unusual

get_sms_format

Function call:
get_sms_format (sms_msg_id integer, lang integer, OUT sms_format character varying) character varying
Schema:
backoffice
Description:
Returns the sms message format in the specified language for a given message (specified by the id of the message in the metadata.sms table

Used for automated system documentation and generation of training materials.

get_sms_format

Function call:
get_sms_format (sms_start_code character varying, lang integer, OUT sms_format character varying) character varying
Schema:
backoffice
Description:
Returns the sms message format in the specified language for a given message (specified by the start code of the message)

Used for automated system documentation and generation of training materials.

get_sms_format_user

Function call:
get_sms_format_user (sms_msg_id integer, uid integer, OUT sms_format character varying) character varying
Schema:
backoffice
Description:
Wrapper for get_sms_format. This version takes the userid and returns the format in the appropriate langage.

get_species_hier_name

Function call:
get_species_hier_name (spec_id integer, user_id integer, OUT species_name character varying) character varying
Schema:
reference
Description:
Returns the highest-level name of the species, based on the species id and the user id to determine the correct language

SELECT get_species_hier_name(speciesid, <<userid>>) FROM anytable;

get_species_name

Function call:
get_species_name (spec_id integer, user_id integer, OUT species_name character varying) character varying
Schema:
reference
Description:
Returns the name of the species, based on the species id and the user id to determine the correct language

SELECT get_species_name(speciesid, <<userid>>) FROM anytable;

get_string

Function call:
get_string (trans_key character varying, OUT st_out character varying) character varying
Schema:
backoffice

get_string

Function call:
get_string (trans_key character varying, user_id integer, OUT st_out character varying) character varying
Schema:
backoffice
Description:
Retuns a string from the tranlsation table (corresponding to the specified key) in the default language (Indonesian)

get_syndrome_name

Function call:
get_syndrome_name (syndromeid integer, user_id integer, OUT syndrome_name character varying) character varying
Schema:
reference
Description:
Returns the name of the syndrome in the language of the viewer (user), given the syndromeid and userid.

SELECT get_syndrome_name(syndromeid, <<userid>>) FROM anytable;

get_table_data

Function call:
get_table_data (jid integer, tid integer, rid integer, sheet character varying, parentrow integer) integer
Schema:
metadata
Description:
This function uses recursion to loop down through subsequent tables / sheets of data to allow insert of subdata from sheets

gettableinfodata

Function call:
gettableinfodata (prjid integer) tableinforecord[]
Schema:
metadata

get_target_name

Function call:
get_target_name (t_code integer, user_id integer, OUT t_name character varying) character varying
Schema:
reference
Description:
The parameters are 1) a targetid, and 2) the userid. The function returns the target name, in the language of the report viewer.

SELECT  get_target_name(targetid, <<userid>>) FROM anytable;

get_tindakan_name

Function call:
get_tindakan_name (tindakan_id integer, user_id integer, OUT tindakan_name character varying) character varying
Schema:
reference
Description:
Returns the name of the tindakan, based on the tindakan id and the user id to determine the correct language

SELECT get_tindakan_name(tindakanid, <<userid>>) FROM anytable;

get_treatment_script

Function call:
get_treatment_script (user_id integer, OUT treatment_code character varying) character varying
Schema:
reference
Description:
This function returns the string that controls formatting of the dose information, according to the viewer's language.

SELECT get_treatment_script(<<userid>>) FROM anytable;

Script (English): '%s head %s @ %s %s with %s'

Script (Indonesian): '%s ekor %s @ %s %s dengan %s'

get_treatmenttype_name

Function call:
get_treatmenttype_name (d_code integer, user_id integer, OUT dt_name character varying) character varying
Schema:
reference
Description:
This functions returns the human readable name of a treatment type, given the treatmenttypeid, returned in the language of the viewer of the report. SELECT get_treatmenttype_name(treatmenttypeid, <<userid>>) FROM ANYTABLE; If no value is provided for the language of the user of the report, the language of the subject of the report is used instead.

gettypeid

Function call:
gettypeid (lookuptype character varying) integer
Schema:
metadata

get_user_groups

Function call:
get_user_groups (userid integer, user_id integer, OUT user_groups character varying) character varying
Schema:
reference
Description:
This functions returns a human readable list of the groups that a user is in, returned in the language of the viewer of the report.

SELECT id, get_user_groups(id, <<userid>>) 
FROM users;

If no value is provided for the language of the user of the report, the language of the subject of the report is used instead.

get_weekday_name

Function call:
get_weekday_name (weekday_id integer, user_id integer, OUT weekday_name character varying) character varying
Schema:
reference
Description:
This function returns the name of the day of the week in the viewer's language. It can be used on a date field as shown:

SELECT get_weekday_name((CASE WHEN EXTRACT(DOW FROM sessionstart::TIMESTAMP)=0 THEN 7 ELSE EXTRACT(DOW FROM sessionstart::TIMESTAMP) END)::integer, <<userid>>) FROM anytable;

gin_extract_query_trgm

Function call:
gin_extract_query_trgm ( text, internal, smallint, internal, internal, internal, internal) internal
Schema:
backoffice

gin_extract_value_trgm

Function call:
gin_extract_value_trgm ( text, internal) internal
Schema:
backoffice

ginint4_consistent

Function call:
ginint4_consistent ( internal, smallint, internal, integer, internal, internal, internal, internal) boolean
Schema:
backoffice

ginint4_queryextract

Function call:
ginint4_queryextract ( internal, internal, smallint, internal, internal, internal, internal) internal
Schema:
backoffice

g_intbig_compress

Function call:
g_intbig_compress ( internal) internal
Schema:
backoffice

g_intbig_consistent

Function call:
g_intbig_consistent ( internal, internal, integer, oid, internal) boolean
Schema:
backoffice

g_intbig_decompress

Function call:
g_intbig_decompress ( internal) internal
Schema:
backoffice

g_intbig_penalty

Function call:
g_intbig_penalty ( internal, internal, internal) internal
Schema:
backoffice

g_intbig_picksplit

Function call:
g_intbig_picksplit ( internal, internal) internal
Schema:
backoffice

g_intbig_same

Function call:
g_intbig_same ( internal, internal, internal) internal
Schema:
backoffice

g_intbig_union

Function call:
g_intbig_union ( internal, internal) integer[]
Schema:
backoffice

g_int_compress

Function call:
g_int_compress ( internal) internal
Schema:
backoffice

g_int_consistent

Function call:
g_int_consistent ( internal, integer[], integer, oid, internal) boolean
Schema:
backoffice

g_int_decompress

Function call:
g_int_decompress ( internal) internal
Schema:
backoffice

g_int_penalty

Function call:
g_int_penalty ( internal, internal, internal) internal
Schema:
backoffice

g_int_picksplit

Function call:
g_int_picksplit ( internal, internal) internal
Schema:
backoffice

gin_trgm_consistent

Function call:
gin_trgm_consistent ( internal, smallint, text, integer, internal, internal, internal, internal) boolean
Schema:
backoffice

g_int_same

Function call:
g_int_same ( integer[], integer[], internal) internal
Schema:
backoffice

g_int_union

Function call:
g_int_union ( internal, internal) integer[]
Schema:
backoffice

gtrgm_compress

Function call:
gtrgm_compress ( internal) internal
Schema:
backoffice

gtrgm_consistent

Function call:
gtrgm_consistent ( internal, text, integer, oid, internal) boolean
Schema:
backoffice

gtrgm_decompress

Function call:
gtrgm_decompress ( internal) internal
Schema:
backoffice

gtrgm_distance

Function call:
gtrgm_distance ( internal, text, integer, oid) double precision
Schema:
backoffice

gtrgm_penalty

Function call:
gtrgm_penalty ( internal, internal, internal) internal
Schema:
backoffice

gtrgm_picksplit

Function call:
gtrgm_picksplit ( internal, internal) internal
Schema:
backoffice

gtrgm_same

Function call:
gtrgm_same ( gtrgm, gtrgm, internal) internal
Schema:
backoffice

gtrgm_union

Function call:
gtrgm_union ( bytea, internal) integer[]
Schema:
backoffice

hierindent

Function call:
hierindent (name character varying, hiercode character varying, html boolean, OUT indented_name character varying) character varying
Schema:
backoffice

hier_parents

Function call:
hier_parents (code character varying, toplevel integer) setof character varying
Schema:
backoffice
Description:
Given a hierachical code in the form 10.2.1.5, this function returns a set of parent codes, up to the specified level, or up to level 1 if not specified. For location codes (without '.') it returns higher level codes.

hiersort

Function call:
hiersort (hiercode character varying, OUT sortcode integer[]) integer[]
Schema:
backoffice

hmac

Function call:
hmac ( bytea, bytea, text) bytea
Schema:
backoffice

hmac

Function call:
hmac ( text, text, text) bytea
Schema:
backoffice

icount

Function call:
icount ( integer[]) integer
Schema:
backoffice

idx

Function call:
idx ( integer[], integer) integer
Schema:
backoffice

im_parse

Function call:
im_parse (msg character varying, user_mail character varying, msg_id bigint) void
Schema:
sms

im_parse_xmpp

Function call:
im_parse_xmpp (msg character varying, user_im character varying, msg_id bigint, OUT insert_id bigint) bigint
Schema:
sms
Description:
This is essentially the same as im_parse, but takes the mobile phone number instead of the email address. Note: Unlike the im_parse() version of the function (which returns void), this is returning the insert id... it was for debugging, is there any reason not to?[bm]
Example call:

SELECT im_parse_xmpp('kode', '081311335256@isikhnas.com', '2233915044836935');

infection_source_report

Function call:
infection_source_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (INFECTION_SOURCE_REPORT(casechid,usrid,1)).*;

insertprojectrecords

Function call:
insertprojectrecords (userid integer, fielddata projfielddata[], tableinfo tableinforecord[], datavaluesall character varying[], auxtbl character varying, parentline integer, tblidx integer, parentrecid integer) inputprocessresult
Schema:
metadata

install_rcmd

Function call:
install_rcmd ( text) text
Schema:
backoffice

intarray_del_elem

Function call:
intarray_del_elem ( integer[], integer) integer[]
Schema:
backoffice

intarray_push_array

Function call:
intarray_push_array ( integer[], integer[]) integer[]
Schema:
backoffice

intarray_push_elem

Function call:
intarray_push_elem ( integer[], integer) integer[]
Schema:
backoffice

_int_contained

Function call:
_int_contained ( integer[], integer[]) boolean
Schema:
backoffice
Description:
contained in

_int_contains

Function call:
_int_contains ( integer[], integer[]) boolean
Schema:
backoffice
Description:
contains

_int_different

Function call:
_int_different ( integer[], integer[]) boolean
Schema:
backoffice
Description:
different

_int_inter

Function call:
_int_inter ( integer[], integer[]) integer[]
Schema:
backoffice

_int_overlap

Function call:
_int_overlap ( integer[], integer[]) boolean
Schema:
backoffice
Description:
overlaps

int_phone

Function call:
int_phone (l_phone bigint, OUT int_phone character varying) character varying
Schema:
reference

int_phone

Function call:
int_phone (l_phone character varying, OUT int_phone character varying) character varying
Schema:
reference

_int_same

Function call:
_int_same ( integer[], integer[]) boolean
Schema:
backoffice
Description:
same as

intset

Function call:
intset ( integer) integer[]
Schema:
backoffice

intset_subtract

Function call:
intset_subtract ( integer[], integer[]) integer[]
Schema:
backoffice

intset_union_elem

Function call:
intset_union_elem ( integer[], integer) integer[]
Schema:
backoffice

_int_union

Function call:
_int_union ( integer[], integer[]) integer[]
Schema:
backoffice

is_child

Function call:
is_child (parent character varying, child character varying, OUT is_child boolean) boolean
Schema:
backoffice
Description:
Determine if any hierarchical code is a child of any other hierarchical code

ischildlocation

Function call:
ischildlocation (parentid integer, locid integer) boolean
Schema:
reference

ischildlocation

Function call:
ischildlocation (parentid integer, locid integer[]) boolean
Schema:
reference

ischildlocation

Function call:
ischildlocation (parentids integer[], locid integer) boolean
Schema:
reference

is_child_sign

Function call:
is_child_sign (parentid integer, childid integer, OUT is_child boolean) boolean
Schema:
backoffice
Description:
Returns a boolean (TRUE,FALSE) depending on whether the sign is a child of another sign.

SELECT is_child_sign(parentsignid, childsignid) FROM anytable;

is_child_sign

Function call:
is_child_sign (parentid integer, childids integer[], OUT is_at_least_one_child boolean) boolean
Schema:
backoffice
Description:
Returns a boolean (TRUE,FALSE) depending on whether at least one of the signs in the array is a child of another sign.

SELECT is_child_sign(parentsignid, childsignidarray) FROM anytable;

is_child_species

Function call:
is_child_species (parentid integer, childid integer, OUT is_child boolean) boolean
Schema:
backoffice

is_child_speciestype

Function call:
is_child_speciestype (parentid integer, childid integer, OUT is_child boolean) boolean
Schema:
backoffice

is_child_speciestype

Function call:
is_child_speciestype (parentids integer[], childid integer) boolean
Schema:
backoffice

is_hpai_compatible_event

Function call:
is_hpai_compatible_event (desa integer, basedate timestamp without time zone, period integer, OUT event boolean) boolean
Schema:
reference
Description:
Determines if there has been an HPAI-compatible event in the specified village in the specified time period. Time period defaults to 14 days, base date defaults to now. The function checks a) for any response (R) report that includes HPAI as the differential diagnosis, and for any priority syndrome report (P) for mortality in poultry that does not yet have a response report.

is_hpai_control_happening

Function call:
is_hpai_control_happening (desa integer, basedate timestamp without time zone, period integer, OUT control boolean) boolean
Schema:
reference
Description:
Determines if there are currently HPAI control activities in the specified village, defined as biosecurity measures reported with Tindakan Lain.

is_hpai_lab_test_pos

Function call:
is_hpai_lab_test_pos (desa integer, basedate timestamp without time zone, period integer, OUT event boolean) boolean
Schema:
reference
Description:
Returns true of there has been at least one positive HPAI rapid test report from the village in the specified time period

is_hpai_rapid_test_pos

Function call:
is_hpai_rapid_test_pos (desa integer, basedate timestamp without time zone, period integer, OUT event boolean) boolean
Schema:
reference
Description:
Returns true of there has been at least one positive HPAI rapid test report from the village in the specified time period

is_in_user_area

Function call:
is_in_user_area (child_userid integer, parent_userid integer) boolean
Schema:
reference

is_in_user_arearev

Function call:
is_in_user_arearev (child_userid integer, parent_userid integer) boolean
Schema:
reference

isnumeric

Function call:
isnumeric ( text) boolean
Schema:
backoffice

is_open_hpai_case

Function call:
is_open_hpai_case (desa integer, basedate timestamp without time zone, period integer, OUT control boolean) boolean
Schema:
reference
Description:
Is there an active (unresolved) case with a definitive diagnosis of HPAI in the village?

is_parent_area

Function call:
is_parent_area (child integer, parent integer, OUT is_parent boolean) boolean
Schema:
backoffice

is_permitted2

Function call:
is_permitted2 (perm integer, uid integer, OUT allowed boolean) boolean
Schema:
backoffice

is_permitted_group

Function call:
is_permitted_group (perm integer, uid integer, OUT allowed boolean) boolean
Schema:
backoffice

is_permitted

Function call:
is_permitted (perm integer, uid integer, OUT allowed boolean) boolean
Schema:
backoffice

is_permitted

Function call:
is_permitted (perm text, uid integer, OUT allowed boolean) boolean
Schema:
backoffice

is_terminal_infrastructure

Function call:
is_terminal_infrastructure (infraid integer, OUT terminal boolean) boolean
Schema:
backoffice
Description:
Identifies which records are the last levels in the hierarchy (have no children)

is_under_surveillance

Function call:
is_under_surveillance (desa integer, basedate timestamp without time zone, period integer, OUT event boolean) boolean
Schema:
reference
Description:
Returns the HPAI status of the specified village: 0: No surveillance in this village; 1: Apparently free; 2: Suspect; 3: Infected

list_functions

Function call:
list_functions (OUT schema character varying, OUT fname character varying, OUT foid oid, OUT returntype character varying, OUT pos integer, OUT direction character, OUT argname character varying, OUT datatype character varying, OUT description character varying) setof record
Schema:
backoffice
Description:
Generates a list of all functions in the database and returns the schema, function name, arguments, return type and description

local_phone

Function call:
local_phone (int_phone character varying, OUT l_phone character varying) character varying
Schema:
backoffice
Description:

SELECT local_phone(phone) FROM anytable;

This function checks to see if the phone number is prepended by '0'. If it is, the phone number is assumed to be already in local format. Otherwise, it is assumed to be in international format, and the first two digits are removed and replaced with '0'.

Note: this function is an improved version of reference.format_phone, but is identical to reference.local_phone; one of these should be removed.

log_error

Function call:
log_error (sms_userid integer, sms_msg character varying) integer
Schema:
sms

log_import_error

Function call:
log_import_error (iref character varying, imsg character varying, isheet character varying) integer
Schema:
metadata

luhn_checksum

Function call:
luhn_checksum (inval bigint) integer
Schema:
reference
Description:
Calculate a Luhn checksum of the supplied number.

Functions to handle Luhn checksums: luhn_checksum, verify_checksum, generate_checkdigit, add_checkdigit, remove_checkdigit.

Code has been modified from http://wiki.postgresql.org/wiki/Luhn_algorithm (with some help from the Python implementation in the above Wikipedia page). luhn_checksum was factored out of the original code so it could be reused in both verify_checksum and verify_checksum.

Also fixed a bug in the original code: LOG(inval) had to be changed to LOG(inval+1) to correctly generate check digits for powers of ten.

Case ids, for example, are stored as integers without checksums. When passed as a reference to external users, a checksum digit is appended. When users supply a case id, it should include the checksum digit, and we verify that it has a checksum of zero. If not, it is an invalid number. This detects most common errors such as single digit error, transpositions, etc. Stripping off the checksum digit will get back the internal caseid.

Tests: the following statements should all evaluate to true.

SELECT luhn_checksum(10000) = 1; SELECT luhn_checksum(10009) = 0; SELECT verify_checksum(10000) = false; SELECT verify_checksum(10009) = true; SELECT generate_checkdigit(1000) = 9; SELECT add_checkdigit(1000) = 10009; SELECT remove_checkdigit(10009) = 1000;

SELECT luhn_checksum(5163103000504460) = 2; SELECT verify_checksum(5163103000504460) = false; SELECT verify_checksum(5163103000504468); SELECT generate_checkdigit(516310300050446) = 8; SELECT add_checkdigit(516310300050446) = 5163103000504468; SELECT remove_checkdigit(5163103000504468) = 516310300050446;

make_regex

Function call:
make_regex (sms_msg_id integer, group_regex boolean) character varying
Schema:
metadata

make_sms_function

Function call:
make_sms_function (sms_msg_id integer) character varying
Schema:
metadata

make_sms_get_vars

Function call:
make_sms_get_vars (sms_msg_id integer, grouped boolean, OUT declarations character varying, OUT funccode character varying, OUT fieldnames character varying[], OUT insertvars character varying[]) record
Schema:
metadata

make_sms_info

Function call:
make_sms_info (sms_msg_id integer) character varying
Schema:
metadata

msg_ah

Function call:
msg_ah (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ai

Function call:
msg_ai (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_bb

Function call:
msg_bb (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_bcs

Function call:
msg_bcs (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_bh

Function call:
msg_bh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_cip

Function call:
msg_cip (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ckab

Function call:
msg_ckab (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ckh

Function call:
msg_ckh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ckhi

Function call:
msg_ckhi (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_cki

Function call:
msg_cki (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ckjs

Function call:
msg_ckjs (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ckl

Function call:
msg_ckl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_cko

Function call:
msg_cko (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ckp

Function call:
msg_ckp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ckpr

Function call:
msg_ckpr (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_cks

Function call:
msg_cks (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ckt

Function call:
msg_ckt (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_cpd

Function call:
msg_cpd (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_cp

Function call:
msg_cp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_csr

Function call:
msg_csr (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_cul

Function call:
msg_cul (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_cv

Function call:
msg_cv (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_cvr

Function call:
msg_cvr (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_d

Function call:
msg_d (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_dh

Function call:
msg_dh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_dhk

Function call:
msg_dhk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_dhm

Function call:
msg_dhm (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_dhp

Function call:
msg_dhp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_dkb

Function call:
msg_dkb (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_dkl

Function call:
msg_dkl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_dnc

Function call:
msg_dnc (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_dp

Function call:
msg_dp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_dsb

Function call:
msg_dsb (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_dx

Function call:
msg_dx (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_gi

Function call:
msg_gi (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_h

Function call:
msg_h (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_hk

Function call:
msg_hk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_hpt

Function call:
msg_hpt (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ib

Function call:
msg_ib (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ih

Function call:
msg_ih (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_image

Function call:
msg_image (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_jbh

Function call:
msg_jbh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_jh

Function call:
msg_jh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_kdp

Function call:
msg_kdp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_kgg

Function call:
msg_kgg (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_kode

Function call:
msg_kode (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_kom

Function call:
msg_kom (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_lab

Function call:
msg_lab (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_lapd

Function call:
msg_lapd (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_lapk

Function call:
msg_lapk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_lapp

Function call:
msg_lapp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_lapsk

Function call:
msg_lapsk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_lh

Function call:
msg_lh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_link

Function call:
msg_link (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_lok

Function call:
msg_lok (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ltl

Function call:
msg_ltl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
backoffice

msg_ltl

Function call:
msg_ltl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_n

Function call:
msg_n (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ob

Function call:
msg_ob (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_obi

Function call:
msg_obi (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_pg

Function call:
msg_pg (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_p

Function call:
msg_p (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_pkb

Function call:
msg_pkb (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_pk

Function call:
msg_pk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_pkl

Function call:
msg_pkl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_pl

Function call:
msg_pl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_pnc

Function call:
msg_pnc (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_pneg

Function call:
msg_pneg (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_pop

Function call:
msg_pop (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_prp

Function call:
msg_prp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_psb

Function call:
msg_psb (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ps

Function call:
msg_ps (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_q

Function call:
msg_q (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_r

Function call:
msg_r (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_rp

Function call:
msg_rp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_rvak

Function call:
msg_rvak (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_sbh

Function call:
msg_sbh (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_sk

Function call:
msg_sk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_sko

Function call:
msg_sko (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_skp

Function call:
msg_skp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_sksr

Function call:
msg_sksr (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_slab

Function call:
msg_slab (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_slap

Function call:
msg_slap (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_snc

Function call:
msg_snc (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_sp

Function call:
msg_sp (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_spk

Function call:
msg_spk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_srab

Function call:
msg_srab (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ssb

Function call:
msg_ssb (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_sur

Function call:
msg_sur (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_tk

Function call:
msg_tk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_tl

Function call:
msg_tl (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_uc

Function call:
msg_uc (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_u

Function call:
msg_u (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_ui

Function call:
msg_ui (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_vak

Function call:
msg_vak (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_vsk

Function call:
msg_vsk (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

msg_z

Function call:
msg_z (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

nama

Function call:
nama (first character varying, sur character varying, OUT nama character varying) character varying
Schema:
reference

nama

Function call:
nama (usrid integer, OUT nama character varying) character varying
Schema:
reference

normal_rand

Function call:
normal_rand ( integer, double precision, double precision) setof double precision
Schema:
backoffice

outcome_report

Function call:
outcome_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (OUTCOME_REPORT(casechid,usrid,1)).*;

parse_xm

Function call:
parse_xm (fname character varying, sender character varying, email_msgid bigint, infname character varying, OUT functionstatus integer, OUT lab2id text) record
Schema:
backoffice

pgp_armor_headers

Function call:
pgp_armor_headers ( text, OUT key text, OUT value text) setof record
Schema:
backoffice

pgp_key_id

Function call:
pgp_key_id ( bytea) text
Schema:
backoffice

pgp_pub_decrypt_bytea

Function call:
pgp_pub_decrypt_bytea ( bytea, bytea) bytea
Schema:
backoffice

pgp_pub_decrypt_bytea

Function call:
pgp_pub_decrypt_bytea ( bytea, bytea, text) bytea
Schema:
backoffice

pgp_pub_decrypt_bytea

Function call:
pgp_pub_decrypt_bytea ( bytea, bytea, text, text) bytea
Schema:
backoffice

pgp_pub_decrypt

Function call:
pgp_pub_decrypt ( bytea, bytea) text
Schema:
backoffice

pgp_pub_decrypt

Function call:
pgp_pub_decrypt ( bytea, bytea, text) text
Schema:
backoffice

pgp_pub_decrypt

Function call:
pgp_pub_decrypt ( bytea, bytea, text, text) text
Schema:
backoffice

pgp_pub_encrypt_bytea

Function call:
pgp_pub_encrypt_bytea ( bytea, bytea) bytea
Schema:
backoffice

pgp_pub_encrypt_bytea

Function call:
pgp_pub_encrypt_bytea ( bytea, bytea, text) bytea
Schema:
backoffice

pgp_pub_encrypt

Function call:
pgp_pub_encrypt ( text, bytea) bytea
Schema:
backoffice

pgp_pub_encrypt

Function call:
pgp_pub_encrypt ( text, bytea, text) bytea
Schema:
backoffice

pgp_sym_decrypt_bytea

Function call:
pgp_sym_decrypt_bytea ( bytea, text) bytea
Schema:
backoffice

pgp_sym_decrypt_bytea

Function call:
pgp_sym_decrypt_bytea ( bytea, text, text) bytea
Schema:
backoffice

pgp_sym_decrypt

Function call:
pgp_sym_decrypt ( bytea, text) text
Schema:
backoffice

pgp_sym_decrypt

Function call:
pgp_sym_decrypt ( bytea, text, text) text
Schema:
backoffice

pgp_sym_encrypt_bytea

Function call:
pgp_sym_encrypt_bytea ( bytea, text) bytea
Schema:
backoffice

pgp_sym_encrypt_bytea

Function call:
pgp_sym_encrypt_bytea ( bytea, text, text) bytea
Schema:
backoffice

pgp_sym_encrypt

Function call:
pgp_sym_encrypt ( text, text) bytea
Schema:
backoffice

pgp_sym_encrypt

Function call:
pgp_sym_encrypt ( text, text, text) bytea
Schema:
backoffice

plr_array_accum

Function call:
plr_array_accum ( double precision[], double precision) double precision[]
Schema:
backoffice

plr_array_push

Function call:
plr_array_push ( double precision[], double precision) double precision[]
Schema:
backoffice

plr_get_raw

Function call:
plr_get_raw ( bytea) bytea
Schema:
backoffice

plr_set_display

Function call:
plr_set_display ( text) text
Schema:
backoffice

plr_set_rhome

Function call:
plr_set_rhome ( text) text
Schema:
backoffice

plr_singleton_array

Function call:
plr_singleton_array ( double precision) double precision[]
Schema:
backoffice

postgres_fdw_validator

Function call:
postgres_fdw_validator ( text[], oid) void
Schema:
backoffice

print_custom_lab_report

Function call:
print_custom_lab_report (labsubmission integer, OUT columns integer, OUT epinumd text, OUT infrastructureid integer, OUT emailto text, OUT response json) record
Schema:
lab

print_custom_lab_report_old

Function call:
print_custom_lab_report_old (labsubmission integer, OUT columns integer, OUT infrastructureid integer, OUT emailto text, OUT response json) record
Schema:
lab

print_custom_lab_test

Function call:
print_custom_lab_test (labsubmission integer, OUT table_data json) json
Schema:
lab

priority_disease_report

Function call:
priority_disease_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (PRIORITY_DISEASE_REPORT(casechid,usrid,1)).*;

put_on_queue

Function call:
put_on_queue (msg json, queue character varying) character varying
Schema:
sms

querytree

Function call:
querytree ( query_int) text
Schema:
backoffice

rapid_tests_report

Function call:
rapid_tests_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (RAPID_TESTS_REPORT(casechid,usrid,1)).*;

rboolop

Function call:
rboolop ( query_int, integer[]) boolean
Schema:
backoffice
Description:
boolean operation with array

remove_checkdigit

Function call:
remove_checkdigit (inval bigint) bigint
Schema:
reference
Description:
Given a number which contains a check digit, return the original number minus the check digit. The caller should check that verify_checksum(inval) is true before calling this function.

rep_ai

Function call:
rep_ai (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data

rep_case

Function call:
rep_case (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data

rep_caseresult

Function call:
rep_caseresult (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data

report_custom_import_errors

Function call:
report_custom_import_errors (fname character varying, email character varying) integer
Schema:
metadata

rep_rabies

Function call:
rep_rabies (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data

rep_response

Function call:
rep_response (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data

rep_signs

Function call:
rep_signs (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data

rep_syndromes

Function call:
rep_syndromes (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data

rep_treatment

Function call:
rep_treatment (caseid integer, sms_userid integer, repdate timestamp without time zone, summary character varying) setof record
Schema:
data

r_max

Function call:
r_max ( integer, integer) integer
Schema:
backoffice

r_max

Function call:
r_max ( integer, integer) integer
Schema:
reference

save_report_file_to_s3

Function call:
save_report_file_to_s3 (filename character varying) integer
Schema:
backoffice

save_report_params_to_ddb

Function call:
save_report_params_to_ddb (rid integer, uid integer, link character varying, params jsonb, srid integer, eventtime timestamp with time zone, runtime numeric) integer
Schema:
backoffice

send_client_report

Function call:
send_client_report (epinumber character varying, labuserid integer) boolean
Schema:
lab

send_email

Function call:
send_email (sendto character varying[], subject character varying, body character varying, files character varying[], html character varying) integer
Schema:
backoffice
Description:
Send an email with specified body text and attachments:

  • sendto: recipient's email address (only one recipient allowed currently)
  • subject: subject line
  • body: body text of the message
  • files: varchar array of file names including paths (must be readable by the db user)

The senders email is automatically set to 'iSIKHNAS <data@isikhnas.com>'

send_error_msg

Function call:
send_error_msg (sms_phone character varying, sms_msg character varying, sms_msgid bigint, sms_userid integer) integer
Schema:
backoffice

send_error_msg

Function call:
send_error_msg (sms_phone character varying, sms_msg character varying, sms_msgid bigint, sms_userid integer) integer
Schema:
sms

sendmessage

Function call:
sendmessage (labid integer, email text, OUT argv text) text
Schema:
backoffice

send_sms

Function call:
send_sms (phone character varying, msg character varying, priority boolean, modem character varying) boolean
Schema:
sms

send_sms_old

Function call:
send_sms_old (phone character varying, msg character varying, priority boolean, modem character varying) character varying
Schema:
sms

send_template_report

Function call:
send_template_report (rep_template character varying, email character varying, recid integer, database character varying) character varying
Schema:
backoffice

set_limit

Function call:
set_limit ( real) real
Schema:
backoffice

settableattributes

Function call:
settableattributes (tblnm character varying) void
Schema:
metadata

show_trgm

Function call:
show_trgm ( text) text[]
Schema:
backoffice

similarity_dist

Function call:
similarity_dist ( text, text) real
Schema:
backoffice

similarity

Function call:
similarity ( text, text) real
Schema:
backoffice

similarity_op

Function call:
similarity_op ( text, text) boolean
Schema:
backoffice

sms_parse

Function call:
sms_parse (msg character varying, msg_type character varying, msg_id bigint) void
Schema:
sms

sms_process

Function call:
sms_process (sms_msg character varying, sms_msgid bigint, sms_userid integer, sms_phone character varying) integer
Schema:
sms

sort_asc

Function call:
sort_asc ( integer[]) integer[]
Schema:
backoffice

sort_desc

Function call:
sort_desc ( integer[]) integer[]
Schema:
backoffice

sort

Function call:
sort ( integer[]) integer[]
Schema:
backoffice

sort

Function call:
sort ( integer[], text) integer[]
Schema:
backoffice

spatial_aggregate_to_new_parent

Function call:
spatial_aggregate_to_new_parent (units integer[], parentname character varying, userid integer) integer
Schema:
reference
Description:
Create a new parent-level spatial unit, by aggregating a group of children units. For example, a contiguous group of desa from several neighbouring kecamatan can be selected and aggregated into a new kecamatan. Inputs: units: an array of unit IDs for the units to be aggregated parentname: the name of the new parent unit to create userid: the id of the current user

spatial_can_edit

Function call:
spatial_can_edit (userid integer) boolean
Schema:
reference
Description:
Given a user id returns true if the user has permission to edit data in the spatial data management system or false otherwise. This is a wrapper function for is_permitted('can_edit_spatial', userid)

spatial_edit

Function call:
spatial_edit (unit integer, newname character varying, newcomment text, userid integer) integer
Schema:
reference
Description:
Edit the name and commnent of spatial data. Inputs: - unit: id of spatial unit - newname: new name - newcomment: new comment - userid: user user id

spatial_explode_desa

Function call:
spatial_explode_desa (desa integer, userid integer) void
Schema:
reference
Description:
Break a multi-part village polygon into multiple seprate villages. Useful for working with islands. Automatcally recodes and names each part sequentially.

spatial_get_parent

Function call:
spatial_get_parent (childid integer) integer
Schema:
reference
Description:
Given a spatial unit id, returns the id of the parent unit.

spatial_get_siblings_without_geom

Function call:
spatial_get_siblings_without_geom (desa integer) setof record
Schema:
reference
Description:
Given a spatial unit id, returns a table consisting of the id, name and code of all sibling units in the same parent unit that do not have any defined boundary geometry

spatial_is_desa

Function call:
spatial_is_desa (desa integer) boolean
Schema:
reference
Description:
Returns true if the id provided is for a desa (unit level 5) or false otherwise

spatial_is_multipart

Function call:
spatial_is_multipart (desa integer) boolean
Schema:
reference
Description:
Returns true if the polygon has more than one parts

spatial_link_previous_record

Function call:
spatial_link_previous_record (original_desa integer, new_desa_to_link integer, new_desa_no_link integer, desa_with_no_geom integer, userid integer) integer
Schema:
reference
Description:
Links an existing manually created spatial unit created through the table interface, to a newly split desa. This function is intended as a means to update manually created spatial units with their boundary data. This function is part of the web-based spatial data management system and is not intended to be called manually. Inputs: - original_desa integer, -- the oirignal desa that has been split into two desa - new_desa_to_link integer, -- the desa split from the original, that is to be linked to a previously manually created record that has no geom - new_desa_no_link integer, -- the split desa that represents the original - no linking required - desa_with_no_geom integer, -- the manually created record with no geom that is to be linked to the new desa that was split from the original - userid integer -- the id of the current user)

spatial_merge_desa

Function call:
spatial_merge_desa (desa1 integer, desa2 integer, userid integer) integer
Schema:
reference
Description:
Given the spatial ids of two villages (desa1 and desa2), merges their boundaries into a single desa, retaining the attributes of desa1 returning the id of the merged village

spatial_parent_is_above_children

Function call:
spatial_parent_is_above_children (units integer[], destination integer) boolean
Schema:
reference
Description:
Check function for the move procedure. Given an array of child unit ids (to recode) and the id of the destination unit, returns true if the destination unit is on level above the child units, otherwise false.

spatial_recode

Function call:
spatial_recode (units integer[], destination integer, userid integer) integer
Schema:
reference
Description:
Move (recode) procedure. Moves units from one parent unit to another. Given an array of units at the same level and in the same parent (both of which should be first checked before this function is called), and the id of the destination parent, this function recodes the child units and any other lower units they contain according to the new parent unit code, and recalculates the boundaries of the parent and any higher units.

spatial_split_desa

Function call:
spatial_split_desa (desa integer, boundary public.geometry, userid integer) setof integer
Schema:
reference
Description:
Split procedure. Splits one desa into two new desa, using a line digitised by the user. Given an id for the desa, and a line geometry unit (in WGS84 coordinates (EPSG:4326)), splits the desa into two new desa, each of which will have identical attributes, and returns the new unit IDs

spatial_units_are_contiguous

Function call:
spatial_units_are_contiguous (units integer[]) boolean
Schema:
reference
Description:
Given an array of units and another unit, determines if all the units are contiguous. If at least unit does not contact any other units, returns false, otherwise true

spatial_units_are_contiguous

Function call:
spatial_units_are_contiguous (units integer[], destination integer) boolean
Schema:
reference
Description:
Given an array of units and another unit, determines if all the units are contiguous. If at least unit does not contact any other units, returns false, otherwise true

spatial_units_at_same_level

Function call:
spatial_units_at_same_level (units integer[]) boolean
Schema:
reference
Description:
Given an array of units, returns true if all the units are at the same level, otherwise false

spatial_units_in_area

Function call:
spatial_units_in_area (units integer[], destination integer, userid integer) boolean
Schema:
reference
Description:
Given an array of units, another unit id, and a user id, returns true if all the units are in the user's area of responsibility, otherwise false

spatial_units_in_area

Function call:
spatial_units_in_area (units integer[], userid integer) boolean
Schema:
reference
Description:
Given an array of units, and a user id, returns true if all the units are in the user's area of responsibility, otherwise false

spatial_units_in_same_grandparent

Function call:
spatial_units_in_same_grandparent (units integer[]) boolean
Schema:
reference
Description:
Given an array of units, returns true if all the units are in the same grandparent unit, otherwise false

spatial_units_in_same_parent

Function call:
spatial_units_in_same_parent (units integer[]) boolean
Schema:
reference
Description:
Given an array of units, returns true if all the units are in the same parent unit, otherwise false

spatial_update_new_desa

Function call:
spatial_update_new_desa (newdesa integer, olddesa integer, new_name character varying) integer
Schema:
reference
Description:
Updates the name and code of a new desa created by spatial_split_desa()

subarray

Function call:
subarray ( integer[], integer) integer[]
Schema:
backoffice

subarray

Function call:
subarray ( integer[], integer, integer) integer[]
Schema:
backoffice

submissions_report

Function call:
submissions_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (submissions_REPORT(casechid,usrid,1)).*;

summarise_data_records

Function call:
summarise_data_records (sdate date, edate date, data_schema character varying[], schema_name text, table_name text, mth date, total integer) setof record
Schema:
backoffice
Description:
Produces a table showing the monthly total newly created records for each data table in the data schema over the specified period. This can be used for monitoring total system usage

tanggal

Function call:
tanggal (repdate timestamp without time zone, OUT formatteddate character varying) character varying
Schema:
reference

testhndlr1

Function call:
testhndlr1 (uid integer, i1 integer, str1 character varying, i2 integer, OUT result integer, OUT outmsg character varying) record
Schema:
data

testhndlr2

Function call:
testhndlr2 (uid integer, i1 integer, str1 character varying, i2 integer, OUT result integer, OUT outmsg character varying) record
Schema:
data

test_r

Function call:
test_r (query text, output text, directory text) text
Schema:
backoffice

testvaliddate

Function call:
testvaliddate (instr character varying) boolean
Schema:
metadata

testvalidoremptydate

Function call:
testvalidoremptydate (instr character varying) boolean
Schema:
metadata

texttodate

Function call:
texttodate (instr character varying) date
Schema:
metadata

tiggetpassword

Function call:
tiggetpassword (_user_id character varying, OUT res_pw character varying) character varying
Schema:
backoffice

tiguserloginplainpw

Function call:
tiguserloginplainpw (_user_id character varying, _user_pw character varying) character varying
Schema:
backoffice

tiguserlogout

Function call:
tiguserlogout ( character varying) void
Schema:
backoffice

track_usage

Function call:
track_usage (user_id integer, mtype character) integer
Schema:
sms

translateinputvalue

Function call:
translateinputvalue (flddata projfielddata, instr character varying, OUT result character varying, OUT errorstr character varying) record
Schema:
metadata

translateinputvalues

Function call:
translateinputvalues (flddata projfielddata[], indata character varying[], tablesused integer[], OUT outdata character varying[], OUT errors cellerrorinfo[]) record
Schema:
metadata

trawl_data

Function call:
trawl_data (numdays integer, OUT reporttype text, OUT locationcode text, OUT locationid integer, OUT speciescode text, OUT speciesid integer, OUT signcode text, OUT signid integer, OUT targetcode text, OUT targetid integer, OUT dx integer) setof record
Schema:
backoffice
Description:
Identifies every unique combination of location, species and result (sign, syndrome, test target or diagnosis) as well as parent values in the corresponding hierarchies, based on data for the specified period, and tests to see if they generate an alert.

treatment_report

Function call:
treatment_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (TREATMENT_REPORT(casechid,usrid,1)).*;

trunc_sms

Function call:
trunc_sms (message character varying, maxparts integer, OUT outmessage character varying) character varying
Schema:
backoffice
Description:
Truncates a string to fit in the specified number of text messages (default is 1). This function leaves enough room for the "[iSIKHNAS]" prefix, adds "..." to indicate truncation. A single SMS maximum is 160 characters, but for spanning SMS, each part can only have 153 characters.

trunc_user_area

Function call:
trunc_user_area (userid integer, level integer, OUT area integer[]) integer[]
Schema:
backoffice
Description:
Takes a user id and (optionally) a number of digits to truncate to. Returns an array of integers, being the ids of the area of responsibility, truncated to the desired level. If truncation level is omitted, defaults to 4 (district/kabupaten). This function is used in report generation to identify the area for the report to be generated. With the default value of 4, users with multiple areas of responsibility wihtin a kabupaten will receive a single report for that kabupaten. Users with multiple areas above the kabupate will have each area included in the report.

uniq

Function call:
uniq ( integer[]) integer[]
Schema:
backoffice

update_ods

Function call:
update_ods (fname character varying) integer
Schema:
backoffice

update_permission

Function call:
update_permission (pid integer, gid integer, perm boolean) integer
Schema:
backoffice

update_permission

Function call:
update_permission (pid integer, gid integer, permint integer) integer
Schema:
backoffice

uploadinputdata

Function call:
uploadinputdata (projid integer, userid integer, instr character varying) inputprocessresult
Schema:
metadata

uuid_generate_v3

Function call:
uuid_generate_v3 (namespace uuid, name text) uuid
Schema:
backoffice

uuid_generate_v5

Function call:
uuid_generate_v5 (namespace uuid, name text) uuid
Schema:
backoffice

vaccination_history_report

Function call:
vaccination_history_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (VACCINATION_HISTORY_REPORT(casechid,usrid,1)).*;

validate_value

Function call:
validate_value (fieldid integer, indata character varying, rowdata character varying[], userid integer, msgid bigint, OUT result businessrulereturn) businessrulereturn
Schema:
metadata
Description:
Validates an input value against metadata. Inputs are:

  • fieldid: the id of the field from the metadata.importfield table
  • indata: the value to be validated, expressed as a string
  • rowdata: a string array of all the values in the row, to be used for cross-field validation
  • userid: the id of the current user
  • msgid: the unique ID of the current message

Outputs: A record with two fields:

  • result: 0 if valid, 1 if invalid
  • error message: Error message explaining why the field is not valid

validintegervalue

Function call:
validintegervalue (instr character varying) boolean
Schema:
metadata

validnumericvalue

Function call:
validnumericvalue (instr character varying) boolean
Schema:
metadata

verify_checksum

Function call:
verify_checksum (inval bigint) boolean
Schema:
reference
Description:
Return true if the supplied input value has a valid check digit as the last digit, false otherwise.

warn_changed_spatial

Function call:
warn_changed_spatial (loc_id integer, user_id integer) integer
Schema:
reference

write_to_s3

Function call:
write_to_s3 (content text, bucket character varying, path text, region_name text) character varying
Schema:
backoffice

xget

Function call:
xget (ref character varying, rowid integer, rowcol character, OUT value character varying) character varying
Schema:
metadata
Description:
Used for parsing imported spreadsheets. xget access data from the xlimp temporary table using 'A1' cell referencing and returns the retrieved cell value

xmpp_parse

Function call:
xmpp_parse (msg character varying, user_phone character varying, msg_id bigint) void
Schema:
sms

zoonotic_involvement_report

Function call:
zoonotic_involvement_report (casechid integer, usrid integer, outform integer, caseid integer, itemdate timestamp without time zone, itemdescription text, item text, reporter text, naturalorder integer) setof record
Schema:
data
Description:
This function is one component of the detailed individual case report. Use 1 for HTML output, 2 for SMS; however, currently, the SMS output is not developed (returns the same as the HTML).

The input parameter are a case identification number (including check digit), the userid, and 1 (for HTML) or 2 (for SMS) output.

SELECT (ZOONOTIC_INVOLVEMENT_REPORT(casechid,usrid,1)).*;