I have took a look directory of public SOAP web services at http://www.service-repository.com. Then an idea came across my mind to utilize gSOAP toolkit to develop some C code for a User Defined Function in PostgreSQL, so I can consume service provided by http://www.currencyserver.de/webservice/currencyserverwebservice.asmx?WSDL to get currency rate through SQL command, such as SELECT provider, source, dest, rate FROM cy(‘USD’, ‘IDR’).

• I download gSOAP on my Ubuntu Laptop. Build and install it into /opt/gsoap directory.
• Use gSOAP wsdl2h to obtain the gSOAP header file cy.h specification of the web service’s WSDL document:
  /opt/gsoap/bin/wsdl2h -c -o cy.h http://www.currencyserver.de/webservice/currencyserverwebservice.asmx?WSDL
• Generate a bunch of source, header, xml and nsmap files based on cy.h header file:
  /opt/gsoap/bin/soapcpp2 -1 -c -C -pcy cy.h
• Copy stdsoap2.c from gSOAP source code distribution into current work directory.
• Create C source code cy.c for PostgreSQL User Defined Functions with Version 1 Calling Conventions:
  #include <postgres.h>
  #include <fmgr.h>
  #ifdef PG_MODULE_MAGIC
  PG_MODULE_MAGIC;
  #endif

  #include <utils/builtins.h>
  #include <funcapi.h>
  #include “cyH.h”
  #include “CurrencyServerWebServiceSoap.nsmap”

  PG_FUNCTION_INFO_V1(cy);
  Datum cy(PG_FUNCTION_ARGS);

  void errExit(struct soap* soap);
  void cleanExit(struct soap* soap);
  struct cyData{ struct soap* soap; char** provList; };

  Datum cy(PG_FUNCTION_ARGS) {

  struct FuncCallContext* funcctx;
  TupleDesc tupdesc;

  if (SRF_IS_FIRSTCALL()) {

  MemoryContext oldcontext;
  funcctx = SRF_FIRSTCALL_INIT();
  oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
  if(get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) {

  ereport(ERROR,

  ( errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
  errmsg(“function returning record called in context “
  “that cannot accept type record”)
  )

  );

  }
  funcctx->tuple_desc=BlessTupleDesc(tupdesc);
  if(PG_NARGS()!=2)

  elog(ERROR, “argument count must be %d”, 2);

  struct cyData* pData=(struct cyData*)palloc(sizeof(struct cyData));
  pData->soap=soap_new();
  struct _ns1__getProviderList reqProvList;
  struct _ns1__getProviderListResponse respProvList;
  soap_default__ns1__getProviderList(pData->soap, &reqProvList);
  soap_default__ns1__getProviderListResponse(pData->soap, &respProvList);
  if(soap_call___ns2__getProviderList(pData->soap, NULL, NULL, &reqProvList, &respProvList)){

  errExit(pData->soap);

  }
  char* delim=”, “;
  char* ch=strtok(respProvList.getProviderListResult,delim);
  int count=0;
  while(ch){

  pData->provList= count==0 ? (char**)palloc(++count*sizeof(char*))
  : (char**)repalloc(pData->provList, ++count*sizeof(char*));
  pData->provList[count-1]=pstrdup(ch);
  ch=strtok(NULL,delim);

  }
  funcctx->max_calls=count;
  funcctx->user_fctx=(void*)pData;

  MemoryContextSwitchTo(oldcontext);

  }

  funcctx = SRF_PERCALL_SETUP();

  if (funcctx->call_cntr max_calls) {

  Datum result;
  HeapTuple tuple;
  bool isnull[]={false,false,false,false};

  struct cyData* pData=(struct cyData*)funcctx->user_fctx;

  struct _ns1__getCurrencyValue req;
  struct _ns1__getCurrencyValueResponse resp;
  soap_default__ns1__getCurrencyValue(pData->soap, &req);

  req.provider=pData->provList[funcctx->call_cntr];
  req.srcCurrency=TextDatumGetCString(PG_GETARG_DATUM(0));
  req.dstCurrency=TextDatumGetCString(PG_GETARG_DATUM(1));
  if(soap_call___ns2__getCurrencyValue(pData->soap, NULL, NULL, &req, &resp)){

  errExit(pData->soap);

  }
  Datum values[]={

  DirectFunctionCall1(textin, CStringGetDatum(pData->provList[funcctx->call_cntr])),
  PG_GETARG_DATUM(0),
  PG_GETARG_DATUM(1),
  DirectFunctionCall1(float8_numeric, Float8GetDatum(resp.getCurrencyValueResult))

  };

  tuple=heap_form_tuple(funcctx->tuple_desc, values, isnull);
  result = HeapTupleGetDatum(tuple);

  SRF_RETURN_NEXT(funcctx, result);

  } else {

  struct cyData* pData=(struct cyData*)funcctx->user_fctx;
  cleanExit(pData->soap);
  SRF_RETURN_DONE(funcctx);

  }

  }

  void errExit(struct soap* soap) {

  char buf[201]=”";
  soap_sprint_fault(soap, buf, 200);
  cleanExit(soap);
  elog(ERROR, “%s”, buf);

  }

  void cleanExit(struct soap* soap) {

  soap_end(soap);
  soap_free(soap);

  }

• Create SQL skeleton for User Defined Functions cy.sql.in:
  CREATE OR REPLACE FUNCTION cy(OUT provider text, INOUT source text, INOUT dest text, OUT rate numeric)
  RETURNS SETOF record
  AS ‘MODULE_PATHNAME’, ‘cy’ LANGUAGE C IMMUTABLE STRICT;
  GRANT EXECUTE ON FUNCTION cy(text, text) TO public;
• Create Makefile to comply with PostgreSQL build infrastructure for extensions (called PGXS):
  MODULE_big = cy
  OBJS = stdsoap2.o cyC.o cyClient.o cy.o
  DATA_built = cy.sql
  PG_CONFIG = /opt/pgsql/bin/pg_config
  PG_CPPFLAGS = -I/opt/gsoap/include
  PGXS := $(shell $(PG_CONFIG) –pgxs)
  include $(PGXS)

  Plase take note that my PostgreSQL resides in /opt/pgsql.

• Build and install everything:
  abdul@mylaptop:~/wsdl/cy$ make
  abdul@mylaptop:~/wsdl/cy$ sudo make install
  abdul@mylaptop:~/wsdl/cy$ /opt/pgsql/bin/psql -U admin -h localhost -p 5432 -d test -f cy.sql
• Login to database server:
  abdul@mylaptop:~/wsdl/cy$ /opt/pgsql/bin/psql -U slony -h localhost -p 5432 test
  Password for user slony:
  psql (9.0.4)
  Type “help” for help.

  test=#

• Check my currency rate…
  test=# SELECT * FROM cy(‘USD’,'IDR’);
  provider | source | dest | rate
  ———-+——–+——+————
  3 | USD | IDR | 9094.34819
  4 | USD | IDR | 9087.5
  AVERAGE | USD | IDR | 9090.92409
  (3 rows)

  test=# SELECT * FROM cy(‘EUR’,'IDR’);
  provider | source | dest | rate
  
  ———-+——–+——+——————
  3 | EUR | IDR | 12020.0213983611
  4 | EUR | IDR | 11993.8496462887
  AVERAGE | EUR | IDR | 12006.9261827403
  (3 rows)

  test=# SELECT * FROM cy(‘SGD’,'IDR’);
  provider | source | dest | rate
  
  ———-+——–+——+——————
  3 | SGD | IDR | 7272.91848469339
  4 | SGD | IDR | 7257.45911065679
  AVERAGE | SGD | IDR | 7265.18348117957
  (3 rows)

  test=#

  Hi, it comes from the Public Web Service thousands miles away from my house, not from my database.

SELECT t.con_received, t.ts FROM (
SELECT con_received, MAX(con_timestamp) ts
FROM    _clstr.sl_confirm GROUP BY con_received
) t ORDER BY t.ts;

Once upon a time, I was in charge to move content of photo file into database’s field, integrated with personal identity table. All works in Linux environment and PostgreSQL 9.0.

I want to share the story….

Original setting: photo file is saved in folder: /home/webuser/photo/ and person table resides in persondb database with DDL as follows:

The table has been populated with hundreds of person records.

Add a field to store photo bytearray:

Create person.c:

Create person.sql.in in the same folder as person.c:

Create Makefile in the same folder as person.c:

In the same folder as Makefile, run command make then make install. Make sure we have sufficient privilege to create file in PostgreSQL contrib and library folder. If everything goes fine then we will have two files:

1. SQL file person.sql in PostgreSQL contrib directory.
2. Library file person.so in PostgreSQL library directory.

Assuming we are in the same folder as person.sql and want to install stored procedure in persondb database (where table person exists) at host server1:

All preparation has been set. It is time to login to PostgreSQL server and run the stored procedure (make sure folder /home/webuser/photo/ is accessible by postgres user):

Wait a moment, let PostgreSQL read photo files and store the binaries into field photo.

My story ends….

I have problem with PgPool II 3.0. I have downloaded the source code from http://pgfoundry.org/projects/pgpool. Extract and compile it successfully. Configure it just to have connection pooling enabled (other features: Replication, LoadBalance, Parallel Query are disabled). But, I can not connect to PostgreSQL 9.0 via PgPool port at 9999 with authentication mode set to md5 in pg_hba.conf (pool_hba.conf is disabled).

After take some time to trace the source code, I have found bug in file pool_auth.c. Inside function:

1. Modify forward declaration of function int read_password_packet(…), add int *pwdSize as the fourth argument:
   //static int read_password_packet(POOL_CONNECTION *frontend, int protoMajor, char *password);

   static int read_password_packet(POOL_CONNECTION *frontend, int protoMajor, char *password, int *pwdSize);

2. Modify function body of function int read_password_packet(…). Watch that *pwdSize has the password length at the end of the function body:
   /*

   * Read password packet from frontend

   */

   //static int read_password_packet(POOL_CONNECTION *frontend, int protoMajor, char *password)

   static int read_password_packet(POOL_CONNECTION *frontend, int protoMajor, char *password, int *pwdSize)

   {

     int size;

     /* Read password packet */

     if (protoMajor == PROTO_MAJOR_V2)

     {

       if (pool_read(frontend, &size, sizeof(size)))

       {

         pool_error(“read_password_packet: failed to read password packet size”);

         return -1;

       }

     }

     else

     {

       char k;

       if (pool_read(frontend, &k, sizeof(k)))

       {

         pool_debug(“read_password_packet_password: failed to read password packet \”p\”");

         return -1;

       }

       if (k != ‘p’)

       {

         pool_error(“read_password_packet_password: password packet does not start with \”p\”");

         return -1;

       }

       if (pool_read(frontend, &size, sizeof(size)))

       {

         pool_error(“read_password_packet_password: failed to read password packet size”);

         return -1;

       }

     }

     if (pool_read(frontend, password, ntohl(size) – 4))

     {

         pool_error(“read_password_packet: failed to read password (size: %d)”, ntohl(size) – 4);

         return -1;

     }

     *pwdSize=size; //now *pwdSize has password length

     return 0;

   }

3. Inside function
   static int do_md5(POOL_CONNECTION *backend, POOL_CONNECTION *frontend, int reauth, int protoMajor)
   add &size as fourth argument to every read_password_packet function call, for example:

   read_password_packet(frontend, protoMajor, password, &size)
4. Recompile and reinstall pgpool project.

Done. Now. I can login to PostgreSQL 9.0 smoothly through PgPool port: 9999.

ERROR: error from Perl function “shadow_trigger_func”: Can’t call method “quote” on an undefined value at line 61

So i go to the mentioned function and found out sub functions: make_pairs, do_insert, do_update and do_delete try to access variables: $data_source_id and $table which are declared locally in main function.

Knowing that problem, I have modified parameter passing as follows:

Within main function:

…
if ($iud->{ $_TD->{new}{iud_action} }) {
   $iud->{ $_TD->{new}{iud_action} }->({
      payload => $_TD->{new},
      tbl => $table,
      source_id => $data_source_id
   });
}
else {
   die “Trigger event was $_TD->{new}{iud_action}<, but should have been one of I, U or D!"
}
…

And inside sub functions: modify $table and $data_source_id into $params->{tbl} and $params->{source_id}, respectively.

CREATE OR REPLACE FUNCTION spline(
IN x_arr numeric[], IN y_arr numeric[], IN resolution numeric,
OUT xs numeric, OUT ys numeric)
RETURNS SETOF record AS
$BODY$
DECLARE
  x numeric[];
  y numeric[];
  e numeric[] DEFAULT ‘{}’::numeric[];
  f numeric[] DEFAULT ‘{}’::numeric[];
  g numeric[] DEFAULT ‘{}’::numeric[];
  r numeric[] DEFAULT ‘{}’::numeric[];
  f2 numeric[] DEFAULT ‘{}’::numeric[];
  count integer;
  i integer;
  j integer;
  idx integer;
  tmp numeric;
BEGIN
  x:=x_arr;
  y:=y_arr;
  count:=ARRAY_UPPER(x,1);
  IF count<3 THEN
    RAISE EXCEPTION ‘sample size must be >=3′;
  END IF;
  IF ARRAY_UPPER(y,1)<>count THEN
    RAISE EXCEPTION ‘x and y array size must be identical’;
  END IF;

——–sort x, ascendingly:
  FOR i IN 1..count-1 LOOP
    idx:=i+1;
    FOR j IN i+2..count LOOP
      IF x[j]<x[idx] THEN
        idx:=j;
      END IF;
    END LOOP;
    IF x[idx]=x[i] THEN
      RAISE EXCEPTION ‘x values must be unique’;
    ELSIF x[idx]<x[i] THEN –swap
      tmp=x[i];
      x[i]=x[idx];
      x[idx]=tmp;
      tmp=y[i];
      y[i]=y[idx];
      y[idx]=tmp;
    END IF;
  END LOOP;

——–prepare variables for Thomas algorithm
  FOR i IN 1..count LOOP
    IF i=1 OR i=count THEN
      e:= e || 0.0;
      f:= f || 0.0;
      g:= g || 0.0;
      r:= r || 0.0;
      f2:=f2 || 0.0;
      CONTINUE;
    END IF;
    e:= e || x[i] – x[i-1];
    f:= f || 2*(x[i+1]-x[i-1]);
    g:= g || x[i+1] – x[i];
    r:= r || ( (6/(x[i+1]-x[i])) * (y[i+1]-y[i]) )
      + ( (6/(x[i]-x[i-1])) * (y[i-1]-y[i]) );
    f2:=f2 || 0.0;
  END LOOP;

——–decomposition
  FOR i IN 2..count LOOP
    IF f[i-1]<>0 THEN
      e[i]:=e[i]/f[i-1];
    END IF;
    f[i]:=f[i]-(e[i]*g[i-1]);
  END LOOP;

——–forward substitution
  FOR i IN 2..count LOOP
    r[i]:=r[i]-(e[i]*r[i-1]);
  END LOOP;

——–backward substitution
  FOR i IN REVERSE count-1..1 LOOP
    IF f[i]<>0 THEN
      f2[i]:=(r[i] – (g[i]*f2[i+1])) / f[i];
    END IF;
  END LOOP;

——–do interpolation
  FOR i IN 2..count LOOP
    xs:=x[i-1]-resolution;
    LOOP
      xs:=xs+resolution;
      EXIT WHEN i=count AND xs>x[i];
      EXIT WHEN i=x[i];
      ys:= ( (f2[i-1] * (x[i]-xs)^3)
        + (f2[i] * (xs-x[i-1])^3)
        ) / (6*(x[i]-x[i-1]));
      ys:= ys + (
        (y[i-1]/(x[i]-x[i-1])) -
        (f2[i-1]*(x[i]-x[i-1])/6)
        ) * (x[i]-xs);
      ys:= ys + (
        (y[i]/(x[i]-x[i-1])) -
        (f2[i]*(x[i]-x[i-1])/6)
        ) * (xs-x[i-1]);
      RETURN NEXT;
      EXIT WHEN xs>=x[i];
    END LOOP;
  END LOOP;

  RETURN;
END;
$BODY$
LANGUAGE ‘plpgsql’ IMMUTABLE STRICT;

SELECT * FROM spline(
  ’{3.3,4.2,6.8,8.2,10.2}’::numeric[],
   ‘{1.2,-1.1,1.5,1.2,2.5}’::numeric[],
  0.1);

xs  ys
3.3  1.199999999999999970
3.4  0.8860175045157231811
3.5  0.5764170295261004851
3.6  0.2755805955257859750
3.7  -0.0121097769905662565
3.8  -0.28227206752830211620
… and so on

  |–Manager1
  |    |
  |    |–Senior1
  |    |    |–Junior1
  |    |    |–Junior2
  |    |
  |    |–Senior2
  |         |–Junior3
  |         |–Junior4
  |  
  |–Manager2
      |
      |–Senior3
      |    |–Junior5
      |    |–Junior6
      |
      |–Senior4
           |–Junior7
           |–Junior8

CREATE OR REPLACE FUNCTION organization(IN trees ltree[]) RETURNS void AS
$BODY$
DECLARE
——–ltree[] will be converted to nested hash:
————-hash_mgr=>hash_snr=>array(Juniors)
    i integer;
    count integer;
    hash_mgr hstore;
    hash_snr hstore;
    tree ltree;
    arr text[];
    mgr text;
    snr text;
    jnr text;
    k1 text; v1 text;
    k2 text; v2 text;
BEGIN
    count:=ARRAY_UPPER(trees,1);
——–convert array of trees into nested hash:
    FOR i IN 1..count LOOP
        tree:=trees[i];
        mgr:=SUBLTREE(tree,0,1);
        snr:=SUBLTREE(tree,1,2);
        jnr:=SUBLTREE(tree,2,3);
        hash_snr:=(hash_mgr->mgr)::hstore;
        arr:=(hash_snr->snr)::text[];
        arr:=COALESCE(arr,’{}’::text[]) || jnr;
        hash_snr:=COALESCE( hash_snr||(snr=>arr::text),snr=>arr::text);
        hash_mgr:=COALESCE( hash_mgr||(mgr=>hash_snr::text),mgr=>hash_snr::text);
    END LOOP;
——–now we hava nested hash
——–usage 1: get Junior1 and Junior2:
    arr:=((hash_mgr->’Manager1′)::hstore)->’Senior1′;
    RAISE NOTICE ‘% %’,arr[1],arr[2];
——–navigate all items
    FOR k1, v1 IN SELECT key, value FROM EACH(hash_mgr) LOOP
        RAISE NOTICE ‘manager: %’,k1;
        FOR k2, v2 IN SELECT key, value FROM EACH(v1::hstore) LOOP
            RAISE NOTICE ‘ senior: %’,k2;
            RAISE NOTICE ‘ junior: %’,(v2::text[])[1];
            RAISE NOTICE ‘ junior: %’,(v2::text[])[2];
        END LOOP;
    END LOOP;
    RETURN;
END;$BODY$
LANGUAGE ‘plpgsql’ STABLE;

SELECT organization(‘{
    Manager1.Senior1.Junior1,
    Manager1.Senior1.Junior2,
    Manager1.Senior2.Junior3,
    Manager1.Senior2.Junior4,
        Manager2.Senior3.Junior5,
        Manager2.Senior3.Junior6,
        Manager2.Senior4.Junior7,
        Manager2.Senior4.Junior8
    }’);

NOTICE:    Junior1 Junior2
NOTICE:    manager: Manager1
NOTICE:        senior: Senior1
NOTICE:                junior: Junior1
NOTICE:                junior: Junior2
NOTICE:        senior: Senior2
NOTICE:                junior: Junior3
NOTICE:                junior: Junior4
NOTICE:    manager: Manager2
NOTICE:        senior: Senior3
NOTICE:                junior: Junior5
NOTICE:                junior: Junior6
NOTICE:        senior: Senior4
NOTICE:                junior: Junior7
NOTICE:                junior: Junior8

CREATE TABLE inventory(
  id serial NOT NULL,
  item text NOT NULL,
  qty integer NOT NULL,
  ver integer NOT NULL DEFAULT 1,
  CONSTRAINT inventory_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE OR REPLACE FUNCTION trig_inventory()
RETURNS trigger AS
$BODY$
BEGIN
  IF (TG_WHEN = ‘BEFORE’ AND TG_OP = ‘INSERT’) THEN
    NEW.ver = 1; –force ver initiated with 1
    RETURN NEW;
  ELSIF (TG_WHEN = ‘BEFORE’ AND TG_OP = ‘UPDATE’) THEN
    IF OLD.ver > NEW.ver THEN –somebody has updated this record
      RAISE EXCEPTION ‘Concurrent Update Protection!’;
    END IF;
    NEW.ver = OLD.ver + 1; –increment UPDATE
    RETURN NEW;
  END IF;
END;
$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;

CREATE TRIGGER inventory_before_insert BEFORE INSERT
  ON inventory FOR EACH ROW EXECUTE PROCEDURE public.trig_inventory();
CREATE TRIGGER inventory_before_update BEFORE UPDATE
  ON inventory FOR EACH ROW EXECUTE PROCEDURE public.trig_inventory();

citra=# INSERT INTO inventory (item,qty) VALUES (‘ITEM_1′,50) RETURNING *;
id | item | qty | ver
—-+——–+—–+—–
2 | ITEM_1 | 50 | 1
(1 row)INSERT 0 1

citra=# BEGIN;
BEGIN
citra=# UPDATE inventory SET qty=qty+100 WHERE id=2 RETURNING *;
id | item | qty | ver
—-+——–+—–+—–
2 | ITEM_1 | 150 | 2
(1 row)UPDATE 1
citra=# COMMIT;

session #1		session #2
citra=# BEGIN;		citra=# BEGIN;
citra=# UPDATE inventory SET qty=qty+100,ver=2 WHERE id=2 RETURNING *;		citra=# UPDATE inventory SET qty=qty+100,ver=2 WHERE id=2 RETURNING *;
citra=# COMMIT;		citra=# COMMIT;
		ERROR: Concurrent Update Protection!

id item    sales_unit amount
1  ITEM_1  UNIT_1     1000.5
2  ITEM_1  UNIT_2     1100
3  ITEM_1  UNIT_3     1150.25
4  ITEM_1  UNIT_1     100.75
5  ITEM_2  UNIT_2     275
6  ITEM_2  UNIT_3     750.5
7  ITEM_2  UNIT_2     300.5

item    UNIT_1   UNIT_2   UNIT_3
ITEM_1  1101.25  1100     1150.25
ITEM_2  0        575.5    750.5

CREATE OR REPLACE FUNCTION sales_amount(
  amounts numeric[],
  unit text,
  amount numeric,
  units text,
  unitcount integer)
RETURNS numeric[] AS
$BODY$
DECLARE
  out_amounts numeric[];
  unit_pattern text;
  sub_unit text[];
  idx integer;
BEGIN
  out_amounts:=amounts;
  ——–units in format: [unit_name]:[array_index],[unit_name]:[array_index],…
  unit_pattern:= unit || ‘:[0-9]{1,}’;
  sub_unit:=regexp_matches(units,unit_pattern);
  IF sub_unit IS NULL THEN
    RETURN out_amounts;
  END IF;
  ——–get array_index for dept
  idx:=split_part(array_to_string(sub_unit,”),’:',2)::integer;
  IF array_upper(out_amounts,1) IS NULL THEN
  ——–initiate amount array with 0
    out_amounts:=string_to_array(
    ’0′ || repeat( ‘,0′, unitcount-1 ) ,
    ’,’ )::numeric[];
  END IF;
  out_amounts[idx]:=out_amounts[idx]+amount;
  RETURN out_amounts;
END;$BODY$
LANGUAGE ‘plpgsql’ IMMUTABLE STRICT;

CREATE AGGREGATE sales_agg(text, numeric, text, integer) (
  SFUNC=sales_amount,
  STYPE=numeric[],
  INITCOND=’{}’
);

CREATE OR REPLACE FUNCTION create_sales_report(
  refcursor, –cursor name
  units text[] –array of your expected units to appear on corss-tab report
)
RETURNS void AS
$BODY$
DECLARE
  unit_count integer;
  units_series text;
  i integer;
  sql text;
BEGIN
  unit_count:=array_upper(units,1);
  ——–convert units into [dept_name]:[array_index],[dept_name]:[array_index],…
  ——–to avoid repetitive loops inside aggregate function
  units_series:=”;
  FOR i in 1..unit_count LOOP
    IF i>1 THEN
      units_series:= units_series || ‘,’;
    END IF;
  units_series:= units_series || units[i] || ‘:’ || i;
  END LOOP;
  ——–compose sql statement
  sql:=    ‘SELECT t.item’;
  i:=1;
  WHILE units[i] IS NOT NULL LOOP
    sql:= sql || ‘,t.amounts[' || i || '] AS ‘ || quote_ident(units[i]);
    i:=i+1;
  END LOOP;
  sql:=sql || ‘ FROM (SELECT item,’
    || ‘sales_agg(sales_unit,amount,’
    || quote_literal(units_series) || ‘,’
    || unit_count || ‘) AS amounts ‘
  || ‘FROM sales GROUP BY item’
  || ‘) t ORDER BY t.item’;
  ——–open cursor
  OPEN $1 FOR EXECUTE sql;
RETURN;
END;$BODY$
LANGUAGE ‘plpgsql’ IMMUTABLE STRICT;

citra=# begin;
BEGIN
citra=# select create_sales_report(‘report’,'{UNIT_1,UNIT_2,UNIT_3}’);
create_sales_report
———————

(1 row)

citra=# FETCH ALL IN report;
item  | UNIT_1  | UNIT_2 | UNIT_3
——–+———+——–+———
ITEM_1 | 1101.25 |   1100 | 1150.25
ITEM_2 |       0 |  575.5 |   750.5
(2 rows)

citra=# COMMIT;

#select create_sales_report(‘report’,'{UNIT_1,UNIT_2,UNIT_3,…,…}’);

#select create_sales_report(‘report’,
  (SELECT array_agg(t.sales_unit) FROM (SELECT DISTINCT ON (sales_unit) sales_unit FROM sales) t)
);