Monthly Archives: February 2009

I have just accomplished a task to transfer tree-formed data structure from client application environment into PostgreSQL PLSQL body function. The tree at client side is structured as shown below:

  |    |
  |    |–Senior1
  |    |    |–Junior1
  |    |    |–Junior2
  |    |
  |    |–Senior2
  |         |–Junior3
  |         |–Junior4
      |    |–Junior5
      |    |–Junior6

Instead of send the item pieces one by one, I want to send all the items at once.

PLSQL Function (convert array of ltree into nested hash)

I decide to package the tree items in the array of LTREE field type provided in PostgreSQL contrib module by Teodor Sigaev and Oleg Bartunov. Later in the function body, I convert the LTREE array into HSTORE (again thanks to Teodor Sigaev and Oleg Bartunov). HSTORE maps key to value. Both are string types. But in my case, the hash contains key which point to another hash, creating nested hash.

Following is the stored procedure in PLSQL created on Postgresql 8.3.1:

CREATE OR REPLACE FUNCTION organization(IN trees ltree[]) RETURNS void AS
——–ltree[] will be converted to nested hash:
    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;
——–convert array of trees into nested hash:
    FOR i IN 1..count LOOP
        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);
——–now we hava nested hash
——–usage 1: get Junior1 and Junior2:
    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;

Let’s start from lowest level. Hash hash_snr maps “senior” to array of juniors.
Go up one level, hash hash_mgr maps “manager” to hash hash_snr.

To use the function, client application should invoke it as follows:

SELECT organization(‘{

Take note RAISE NOTICE at the lower portion of function body. It will give us messages as follows:

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