Unexpected behavior in returning composite type for plpgsql function

Question

So, I'm stuck with some issue on plpgsql, and I don't know what I may be doing wrong. I created a composite type, which I use as return in a plpgsql function, for obtaining it in another. The problem is that this doesn't behave the way I expected.

Here's my composite type:

CREATE TYPE sync.sync_conn_data AS (
  sync_conn_name VARCHAR,
  sync_active BOOLEAN,
  sync_use_ssl BOOLEAN,
  lcl_ctry VARCHAR,
  lcl_dbhostaddr VARCHAR,
  lcl_dbname VARCHAR,
  lcl_dbuser VARCHAR,
  lcl_dbpasswd VARCHAR,
  rmte_ctry VARCHAR,
  rmte_dbhostaddr VARCHAR,
  rmte_dbname VARCHAR,
  rmte_dbuser VARCHAR,
  rmte_dbpasswd VARCHAR,
  timezone VARCHAR
);

Here's the function that returns it:

CREATE OR REPLACE FUNCTION sync.sync_connect (
    param_name VARCHAR = 'sync_db'::VARCHAR
)
RETURNS sync.sync_conn_data AS
$body$
DECLARE
    sync_connrcd sync.sync_conn_data;
BEGIN
    WITH all_conn_prms AS (
        SELECT (CASE WHEN param_name = 'local_country' THEN param_value ELSE NULL END) AS local_country,
               (CASE WHEN param_name = 'sync_active' THEN param_value ELSE NULL END) AS sync_active,
               (CASE WHEN param_name = 'sync_use_ssl' THEN param_value ELSE NULL END) AS sync_use_ssl,
               (CASE WHEN param_name = 'ago_dbhostaddr' THEN param_value ELSE NULL END) AS ago_dbhostaddr,
               (CASE WHEN param_name = 'ago_dbname' THEN param_value ELSE NULL END) AS ago_dbname,
               (CASE WHEN param_name = 'ago_dbuser' THEN param_value ELSE NULL END) AS ago_dbuser,
               (CASE WHEN param_name = 'ago_dbpasswd' THEN param_value ELSE NULL END) AS ago_dbpasswd,
               (CASE WHEN param_name = 'cub_dbhostaddr' THEN param_value ELSE NULL END) AS cub_dbhostaddr, 
               (CASE WHEN param_name = 'cub_dbname' THEN param_value ELSE NULL END) AS cub_dbname,
               (CASE WHEN param_name = 'cub_dbuser' THEN param_value ELSE NULL END) AS cub_dbuser,
               (CASE WHEN param_name = 'cub_dbpasswd' THEN param_value ELSE NULL END) AS cub_dbpasswd
        FROM sync.sync_config_params
        WHERE param_name IN ('local_country','sync_active','sync_use_ssl','ago_dbhostaddr','ago_dbname','ago_dbuser','ago_dbpasswd','cub_dbhostaddr','cub_dbname','cub_dbuser','cub_dbpasswd')
    ),
    fltd_conn_prms AS (
        SELECT string_agg(all_conn_prms.local_country, ',') AS local_country,
               string_agg(all_conn_prms.sync_active, ',') AS sync_active,
               string_agg(all_conn_prms.sync_use_ssl, ',') AS sync_use_ssl,
               string_agg(all_conn_prms.ago_dbhostaddr, ',') AS ago_dbhostaddr,
               string_agg(all_conn_prms.ago_dbname, ',') AS ago_dbname,
               string_agg(all_conn_prms.ago_dbuser, ',') AS ago_dbuser,
               string_agg(all_conn_prms.ago_dbpasswd, ',') AS ago_dbpasswd,
               string_agg(all_conn_prms.cub_dbhostaddr, ',') AS cub_dbhostaddr,
               string_agg(all_conn_prms.cub_dbname, ',') AS cub_dbname,
               string_agg(all_conn_prms.cub_dbuser, ',') AS cub_dbuser,
               string_agg(all_conn_prms.cub_dbpasswd, ',') AS cub_dbpasswd
        FROM all_conn_prms
    )
    SELECT coalesce(sync_conn_name, 'sync_db') AS sync_conn_name,
           fltd_conn_prms.sync_active,
           fltd_conn_prms.sync_use_ssl,
           fltd_conn_prms.local_country AS lcl_ctry,
           (CASE fltd_conn_prms.local_country WHEN 'AGO' THEN fltd_conn_prms.ago_dbhostaddr WHEN 'CUB' THEN fltd_conn_prms.cub_dbhostaddr ELSE NULL END) AS lcl_dbhostaddr,
           (CASE fltd_conn_prms.local_country WHEN 'AGO' THEN fltd_conn_prms.ago_dbname WHEN 'CUB' THEN fltd_conn_prms.cub_dbname ELSE NULL END) AS lcl_dbname,
           (CASE fltd_conn_prms.local_country WHEN 'AGO' THEN fltd_conn_prms.ago_dbuser WHEN 'CUB' THEN fltd_conn_prms.cub_dbuser ELSE NULL END) AS lcl_dbuser,
           (CASE fltd_conn_prms.local_country WHEN 'AGO' THEN fltd_conn_prms.ago_dbpasswd WHEN 'CUB' THEN fltd_conn_prms.cub_dbpasswd ELSE NULL END) AS lcl_dbpasswd,
           (CASE fltd_conn_prms.local_country WHEN 'AGO' THEN 'CUB' WHEN 'CUB' THEN 'AGO' ELSE NULL END) AS rmte_ctry,
           (CASE fltd_conn_prms.local_country WHEN 'AGO' THEN fltd_conn_prms.cub_dbhostaddr WHEN 'CUB' THEN fltd_conn_prms.ago_dbhostaddr ELSE NULL END) AS rmte_dbhostaddr,
           (CASE fltd_conn_prms.local_country WHEN 'AGO' THEN fltd_conn_prms.cub_dbname WHEN 'CUB' THEN fltd_conn_prms.ago_dbname ELSE NULL END) AS rmte_dbname,
           (CASE fltd_conn_prms.local_country WHEN 'AGO' THEN fltd_conn_prms.cub_dbuser WHEN 'CUB' THEN fltd_conn_prms.ago_dbuser ELSE NULL END) AS rmte_dbuser,
           (CASE fltd_conn_prms.local_country WHEN 'AGO' THEN fltd_conn_prms.cub_dbpasswd WHEN 'CUB' THEN fltd_conn_prms.ago_dbpasswd ELSE NULL END) AS rmte_dbpasswd,
           current_setting('TIMEZONE') AS timezone
    INTO sync_connrcd
    FROM fltd_conn_prms;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Exception text...';
    END IF;

    RETURN sync_connrcd;
EXCEPTION
    WHEN others THEN
        RAISE NOTICE 'SQLERRM -> %', quote_nullable(SQLERRM);
        INSERT INTO sync.sync_exec_log (log_type, log_msg) VALUES (1, SQLERRM);
        RETURN sync_connrcd;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

And here's the function where I expect the result:

CREATE OR REPLACE FUNCTION sync.validate_config (
    // input params...
)
RETURNS pg_catalog.void AS
$body$
DECLARE
    conn_data sync.sync_conn_data;
    // ... 
BEGIN
    SELECT sync.sync_connect('sync_cfg_mgmt') INTO conn_data;

    IF conn_data.lcl_ctry IS NULL OR conn_data.lcl_dbname IS NULL OR conn_data.lcl_dbhostaddr IS NULL OR conn_data.lcl_dbuser IS NULL OR conn_data.lcl_dbpasswd IS NULL OR conn_data.rmte_ctry IS NULL OR conn_data.rmte_dbname IS NULL OR conn_data.rmte_dbhostaddr IS NULL OR conn_data.rmte_dbuser IS NULL OR conn_data.rmte_dbpasswd IS NULL THEN
        RAISE EXCEPTION 'No data obtained';
    END IF;

    // more code ...
EXCEPTION
    WHEN others THEN
        RAISE NOTICE 'SQLERRM -> %', quote_nullable(SQLERRM);
        INSERT INTO sync.sync_exec_log (log_type, log_msg) VALUES (1, quote_nullable(SQLERRM));
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

The funny fact is that, using the EMS SQL Manager for PostgreSQL for debugging, this code works perfectly, but when executed the function sync.validate_config() through any client, throws the exception of no data obtained, 'cause all the fields in the conn_data object are null, except for the first one, which contains all the object info. This is the output I get when putting this multiple RAISE NOTICE after getting the result in the function:

RAISE NOTICE '%', conn_data.sync_conn_name;
RAISE NOTICE '%', conn_data.sync_active;
RAISE NOTICE '%', conn_data.sync_use_ssl;
RAISE NOTICE '%', conn_data.lcl_ctry;
RAISE NOTICE '%', conn_data.lcl_dbhostaddr;
RAISE NOTICE '%', conn_data.lcl_dbname;
RAISE NOTICE '%', conn_data.lcl_dbuser;
RAISE NOTICE '%', conn_data.lcl_dbpasswd;
RAISE NOTICE '%', conn_data.rmte_ctry;
RAISE NOTICE '%', conn_data.rmte_dbhostaddr;
RAISE NOTICE '%', conn_data.rmte_dbname;
RAISE NOTICE '%', conn_data.rmte_dbuser;
RAISE NOTICE '%', conn_data.rmte_dbpasswd;
RAISE NOTICE '%', conn_data.timezone;

Please, does anyone knows what's happening here?

Thanks in advance.

Answer 1

Since nobody answered my question, and I was in a little hurry, I looked for alternatives to solve this problem. I share now the solution I found, in case anyone else has had this kind of problem.

I used de plpgsql hstore module for the returning type of the sync.sync_connect() function, in this way:

CREATE OR REPLACE FUNCTION sync.sync_connect (
    sync_conn_name VARCHAR = 'sync_db'::VARCHAR
)
RETURNS hstore AS
$body$
DECLARE
    sync_connrcd sync.sync_conn_data;
BEGIN
    /* Same code as before, where sync_connrcd is filled */

    RAISE NOTICE 'conn_data -> %', row_to_json(sync_connrcd);
    RETURN hstore(sync_connrcd);
EXCEPTION
    WHEN others THEN
        RAISE NOTICE 'SQLERRM -> %', quote_nullable(SQLERRM);
        INSERT INTO sync.sync_exec_log (log_type, log_msg) VALUES (1, SQLERRM);
        RETURN hstore(sync_connrcd);
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

And then, in the sync.validate_config() function I stored the result into an hstore object, this way:

CREATE OR REPLACE FUNCTION sync.validate_config (
    /* input params */
)
RETURNS pg_catalog.void AS
$body$
DECLARE
    conn_data hstore;
    /* more variables */
BEGIN
    SELECT sync.sync_connect('sync_cfg_mgmt') INTO conn_data;

    IF NOT exist(conn_data, 'lcl_ctry') OR NOT defined(conn_data, 'lcl_ctry') OR 
       NOT exist(conn_data, 'lcl_dbname') OR NOT defined(conn_data, 'lcl_dbname') OR 
       NOT exist(conn_data, 'lcl_dbhostaddr') OR NOT defined(conn_data, 'lcl_dbhostaddr') OR 
       NOT exist(conn_data, 'lcl_dbuser') OR NOT defined(conn_data, 'lcl_dbuser') OR 
       NOT exist(conn_data, 'lcl_dbpasswd') OR NOT defined(conn_data, 'lcl_dbpasswd') OR 
       NOT exist(conn_data, 'rmte_ctry') OR NOT defined(conn_data, 'rmte_ctry') OR 
       NOT exist(conn_data, 'rmte_dbname') OR NOT defined(conn_data, 'rmte_dbname') OR 
       NOT exist(conn_data, 'rmte_dbhostaddr') OR NOT defined(conn_data, 'rmte_dbhostaddr') OR 
       NOT exist(conn_data, 'rmte_dbuser') OR NOT defined(conn_data, 'rmte_dbuser') OR 
       NOT exist(conn_data, 'rmte_dbpasswd') OR NOT defined(conn_data, 'rmte_dbpasswd') THEN
        RAISE EXCEPTION 'No data obtained';
    END IF;

    /* more code */
EXCEPTION
    WHEN others THEN
        RAISE NOTICE 'SQLERRM -> %', quote_nullable(SQLERRM);
        INSERT INTO sync.sync_exec_log (log_type, log_msg) VALUES (1, SQLERRM);
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

And to access any of the values within the object, I do it as follows conn_data->'rmte_dbname'.

That's all, it works perfectly fine for me.