Handling Global Associative Arrays in PostgreSQL

While migrating from Oracle to PostgreSQL, most of the developers ask about the options available in Handling Global associative arrays in PostgreSQL. It might get challenging if the variables need to be accessible in the Exception block. To avoid developers from finding it difficult while performing conversions of PL/SQL from Oracle to PostgreSQL, we are writing this blog post to demonstrate the workarounds available with not much of additional programming effort.

By the way, the fact to note after reading this blog post is that, several features you want to observe in PostgreSQL during migrations or Code conversions are available without the need of an additional Enterprise license with PostgreSQL (which could create a vendor lock-in).

Ask us about possibilities with Community PostgreSQL (Open Source) before switching to an Enterprise licensing. 🙂

What is an Associative Array ?

Before getting to the workarounds, let us understand what is an associative array. In programming languages, an associative array may either be called a map or a dictionary. Associative array is a collection of (key, value) pairs where a key associates to a value. This association between a key and a value may be referred to as mapping. The key can either be of a text or a numeric type that can be mapped to any value type. 

PostgreSQL Array type

PL/pgSQL in PostgreSQL allows us to declare a variable as an ARRAY type. This ARRAY can be either a base or a custom type. For example, if we want to store a list of PINCODE values, then, we can declare the variable as v_pincode INT[]. Once we declare this variable, then, we can load all the pincode values from a table/view/function into this array. 

We can also initialize the v_pincode array type with a static set of pincode values. Here, the v_pincode holds all the values and we can fetch a specific state’s pincode value, by providing an index. Which means, if we want to access the 1st state pincode, then, we can access the value using v_pincode[1]. If we want to access the pincode value of 3rd state, then, we have to pass the index value 3 using v_pincode[3].

Here is a sample code to illustrate the above with an example.

postgres=> CREATE OR REPLACE FUNCTION process_orders() RETURNS BOOL
AS
$$
DECLARE
--initializing static pincodes
v_pincode INT[]=ARRAY[123456, 123457, 123458, 123459];
BEGIN
RAISE NOTICE '1st state pincode %', v_pincode[1];
RAISE NOTICE '3rd state pincode %', v_pincode[3];
RETURN true;
END;
$$ LANGUAGE PLPGSQL;
CREATE FUNCTION

Let us execute the function created above and see the results.

postgres=> SELECT process_orders();
NOTICE: 1st state pincode 123456
NOTICE: 3rd state pincode 123458
process_orders
----------------
t
(1 row)

We got the results as expected. However, this code has some limitations because we are unable to fetch the values based on their keys. For example, the pincodes (1st and the 3rd elements) are fetched using their Indexes. If we are able to access the same pincodes based on their keys, then, the lookup will be more powerful. So, how to approach this requirement ? Let us discuss the approach further. 

Associating or mapping a name or a key to a value

Thanks to PostgreSQL’s rich data types such as hstore or json which can be leveraged to perform such mapping.

Following is a sample code using a json static object.

CREATE OR REPLACE FUNCTION process_orders() RETURNS BOOL
AS
$$
DECLARE
--initializing static pincodes
v_pincode JSON= '{"CA": 123456, "AZ": 123457, "OH": "123458", "CO": 123459}';
BEGIN
RAISE NOTICE 'CA state pincode %', v_pincode->>'CA';
RAISE NOTICE 'OH state pincode %', v_pincode->>'OH';
RETURN true;
END;
$$ LANGUAGE PLPGSQL;

Let us execute the above created function and see the results.

postgres=> SELECT process_orders();
NOTICE:  CA state pincode 123456
NOTICE:  OH state pincode 123458
process_orders
----------------
t
(1 row)

Now, we are able to make the lookup using a key possible with our PL/pgSQL code. Thus, by using a json type instead of an ARRAY we can implement the concept of associative arrays. 

Global Associative Arrays

The json approach demonstrated above seems to be perfect, but it does not solve the problem of global or session level associative arrays. What this means is that the same associative array cannot be accessed using another function running in the same session. This is because of the lack of global variables or global arrays. 

How to make Global Associate Arrays possible with PostgreSQL ?

Following are the 5 possible solutions with some or no limitations to each approach. 

  1. Declare the same json object in the other function.

  • Requires duplicating the same variable and the same context. 

  1. Pass the json argument as a parameter to the other function.

  • Pass the same value across functions. However, the argument lists may become huge to manage or debug.

  1. Store this object in a table and load it in the function.

  • Storing it in a Table and accessing the elements by selecting from the Table. This can be a heavy overhead. 

  1. Use set_config() approach, which set the object at a session level, and read it from the current_setting(). 

  • If any value is set, an Exception block cannot view the configured settings. May become challenging while performing conversions from Oracle to PostgreSQL where a good amount of logic is written in the Exception block. May be great when we are not accessing any global variables in the exception block. We will be discussing this in detail in our future blog post. 

  1. Using a supported PostgreSQL language context. 

  • With this approach, we should be able to access the global variables across the exception blocks and also the non exception blocks.

The first 4 approaches seem to be pretty straight forward and simple with their own limitations. But the last approach about using another language’s session context is something interesting and we are discussing that further.

Using TCL to support Global Associative Arrays

As we all know, PostgreSQL supports many trusted procedural languages such as PLPerl, PLPython, PLTCL, PLv8 etc. All these trusted languages guarantee that the program code written in that language will not be accessing the underlying physical files. Out of all these programming languages, we will be choosing a simple, safe and a powerful language called tickle (PLTCL). 

TCL language does support the associative array concepts and we will be leveraging this feature in a global way. This will give more flexibility of lookup of values using keys. Let us see the following example code to see how we can make the declaration of a global variable with tickle (TCL).

TCL instructions
As the first step, let’s declare a global variable (namespace) using the following TCL code.

$ tclsh
% # Here, arr_ptr is a pointer to the namespace/global variable "pincodes"
% upvar 0 ::"pincodes" arr_ptr

Now, let’s add a pincode value to this namespace (or the global variable).

% set arr_ptr('CA') 123456
123456

Let us now get the value which we set in the associative array.

% set arr_ptr('CA')
123456

Using the simple example created above, we have seen how we can declare a global variable and store the values using TCL.

Using PLTCL to support Global Associative Arrays in PostgreSQL.

To achieve the functionality of global associative arrays, let us embed the same code in PLTCL as seen in the following steps.

Step 1 : Create the PLTCL Extension

postgres=> CREATE EXTENSION pltcl;
CREATE EXTENSION

Step 2 : Create a function using PLTCL to declare the Global Associative Array and set a value.

postgres=> CREATE OR REPLACE FUNCTION pltcl_set(TEXT, TEXT, TEXT) RETURNS VOID AS $$
upvar 0 ::$1 arr_ptr
set arr_ptr($2) $3
$$ LANGUAGE PLTCL;
CREATE FUNCTION

Step 3 : Create a function using PLTCL to get the value mapped to the key.

postgres=> CREATE OR REPLACE FUNCTION pltcl_get(TEXT, TEXT) RETURNS TEXT AS $$
upvar 0 ::$1 arr_ptr
set arr_ptr($2)
$$ LANGUAGE PLTCL;
CREATE FUNCTION

Testing

Let us perform a test using the newly created PLTCL functions.

postgres=> SELECT pltcl_set('pincodes', 'CA', '123456');
pltcl_set
-----------
(1 row)
postgres=> SELECT pltcl_get('pincodes', 'CA');
pltcl_get
-----------
123456
(1 row)

As seen in the above example, we are able to get data from the TCL context which is global. Which means, we are able to set the pincodes from one statement (using pltctl_set() ), and able to access it’s value from another statement (using pltcl_get()). 

Iterating over the Global Associative Arrays

What is an array without an iterator, right? Let us leverage the same TCL code and create another function which iterates over the global array.

postgres=> CREATE OR REPLACE FUNCTION pltcl_itr(TEXT) RETURNS TABLE(key TEXT, value TEXT)
AS
$$
upvar 0 ::$1 arr_ptr
    foreach i  [array names arr_ptr] {
        return_next [list key $i value [set arr_ptr($i)]]
    }
$$ LANGUAGE PLTCL;
CREATE FUNCTION

Now, let’s add a few more entries to `pincodes` global array and do the iteration test.

postgres=> SELECT pltcl_set('pincodes', 'AZ', '123457');
pltcl_set
-----------
(1 row)
postgres=> SELECT pltcl_set('pincodes', 'OH', '123458');
pltcl_set
-----------
(1 row)
postgres=> SELECT pltcl_set('pincodes', 'CO', '123459');
pltcl_set
-----------
(1 row)

Let’s use the iterator function to get all the lists of available pincodes from the global array, as seen in the following block.

postgres=> SELECT * FROM pltcl_itr('pincodes');
key | value
-----+--------
CA  | 123456
CO  | 123459
OH  | 123458
AZ  | 123457
(4 rows)

As seen above, this iterator is actually helping to iterate over the global associative array. We can use these values across multiple functions across the same session. 

Conclusion

PostgreSQL offers a lot of flexibility in writing the code in multiple languages, which eventually enables us to inherit the power of other programming languages. As you have witnessed in this blog post, we leveraged the TCL’s features and are able to achieve the global associative arrays, which is only available in proprietary databases like Oracle. 

While you are migrating from Oracle to PostgreSQL, we may come across a similar scenario where a package is using a global associative array. With the help of PLTCL or other safe interpreter languages, we can easily achieve this functionality in PostgreSQL.

Did you read our blog post on the Role of Foreign Data Wrappers while performing Migrations to PostgreSQL ? Subscribe to our blog posts to stay updated on our latest posts on Migrations to PostgreSQL.
Thinking of migrating to Open Source PostgreSQL and avoid Vendor lock-in ? Reach out to MigOps to know how we can help you.

Subscribe to our Newsletters.

16 thoughts on “Handling Global Associative Arrays in PostgreSQL”

  1. The worst part is associative array description. Did you ever work with them in Oracle ?
    All proposed solution – JSON, ARRAY, TCL are too naive and incorrect if it is associative array.

    1. Dear Oracle expert, Yes i did work with them in Oracle. Let me also share you a Wikipedia link to help you with some description on Associate Arrays in detail.
      https://en.wikipedia.org/wiki/Associative_array
      By the way, what do you mean by TCL not being a correct approach in the example discussed in this article ? When you say the solution or the work-around is Incorrect, can you explain your statement in detail with an example ? Where is it wrong ? Why is this incorrect ?
      We leveraged TCL associative arrays, which is indeed much better than Oracle Associative arrays in my opinion 🙂

          1. SORT
            We know that, Oracle Associate arrays do saves key in sorted order.
            While we do FIRST, LAST functionality, it will return the first and last element of the array based on sorting. This is neither impossible nor tough with TCL, and also the simple ORDER BY clause will take care of it. See the TCL lsort command, which do sort the arrays based on key values.

            NULL
            In TCL, everything is a text including NULL. So, we are good having NULL as a key for the associate array.

            JSON
            Agreed, JSONB has the concept of unique keys in PG, Let us change the example to JSONB rather JSON

      1. What particular part of TCL approach is better ? No way to pass array as an argument ? No collection constructors ? No defaults ? Is it possible to store array as a value of other array ?
        These are only several questions. You’ll have more trying to implement migration solution

        1. Would like to request you to see what TCL is offering to us, with the below link.
          https://www.tcl.tk/man/tcl/TclCmd/contents.htm

          We all should agree that, Oracle is a software which is written in a programming language.
          The concept of associative arrays in any database software, must be using it’s language specific programming feature like Set, Map, UnorderedMap, List etc.

          Like other programming languages, TCL is also offering to use the same features.
          And, I see there is no harm in leveraging the TCL offered associate arrays, as a replacement to Oracle’s associative arrays.
          Do you see any reason why we can not use this?

          For the use cases you have mentioned, I think we can build a migration solution with TCL, which will solve the compatibility problems.

  2. The way you handled and implemented the Global Variable is the most interesting part in this article.
    Excellent post.

  3. I only see a typo in the multi line string, where we need to have $$ in the place “$” (DECLARE, LANGUAGE)

Leave a Comment

Your email address will not be published.

Scroll to Top