Tree-structured data and Nested Hash

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:

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

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
$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;

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(‘{
    Manager1.Senior1.Junior1,
    Manager1.Senior1.Junior2,
    Manager1.Senior2.Junior3,
    Manager1.Senior2.Junior4,
        Manager2.Senior3.Junior5,
        Manager2.Senior3.Junior6,
        Manager2.Senior4.Junior7,
        Manager2.Senior4.Junior8
    }’);

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
Advertisements
6 comments
  1. In 8.4, you’ll be able to use Common Table Expressions for this kind of thing 🙂

  2. I love your site!

    _____________________
    Experiencing a slow PC recently? Fix it now!

  3. This is quite a hot info. I’ll share it on Facebook.

  4. hello!,I really like your writing so so much! proportion we keep up a correspondence more about your post on AOL? I need an expert in this space to resolve my problem. May be that’s you! Looking forward to look you.

  5. I delight in, lead to I discovered exactly what I used to be having a look for. You have ended my four day long hunt! God Bless you man. Have a nice day. Bye

  6. net said:

    As a website resource for enterprises and know-how enthusiasts to stick to the latest and biggest advancements in Unified Communications, IP Telephony, Hosted Communications and VoIP.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: