As part of a bigger project I am writing a bunch of server-side scripts in PostgreSQL. One of them takes a subset of the DB's tables and a WHERE clause and then returns the union of the filtered results. I am writing the script incrementally and I currently have:

DROP FUNCTION api.func;
CREATE OR REPLACE FUNCTION api.func
(
    _tables    json,
    conditions json
)
RETURNS text AS 
$$
DECLARE
    _key         text ; 
    _value       text ;
    _table       text ;
    where_clause text ;
    query_string text ;
BEGIN
    where_clause := 'WHERE' ;
    FOR _key, _value IN
        SELECT * FROM json_each_text(conditions)
    LOOP
        where_clause := where_clause || ' ' || _key || ' = ' || '''' || _value || '''' || ' AND' ;
    END LOOP ;
    where_clause = TRIM(TRAILING 'AND' FROM where_clause) ;
    FOR _table IN
        SELECT * FROM json_array_elements_text(_tables -> 'tabnames')
    LOOP
        query_string := query_string || ' (SELECT * FROM ' || _table || ' ' || where_clause || ') UNION' ;
        RAISE NOTICE 'Query string now: %', query_string ;
    END LOOP ;
    query_string = TRIM(TRAILING 'UNION' FROM query_string) ;
    RETURN query_string ;
END ;
$$ LANGUAGE plpgsql ;

However, when I call the function with

SELECT api.func('{"tabnames" : ["tabname1", "tabname2"]}'::json, '{"col" : "val"}'::json);

I get <NULL> as a result. I RAISE NOTICE'd within the loop to confirm and the variable query_string does not get updated.

CodePudding user response：

You didn't initialize query_string.

query_string := query_string ...

is: query_string := null ...

CodePudding user response：

where_clause := where_clause || ' ' => results in NULL because where_clause is NULL after declaration. Declare it as an empty string '' or use COALESCE():

DROP FUNCTION api.func;
CREATE OR REPLACE FUNCTION api.func
(
    _tables    json,
    conditions json
)
RETURNS text AS 
$$
DECLARE
    _key         text := ''; -- initial empty string
    _value       text := '';
    _table       text := '';
    where_clause text := '';
    query_string text := '';
BEGIN
    where_clause := 'WHERE' ;
    FOR _key, _value IN
        SELECT * FROM json_each_text(conditions)
    LOOP
        where_clause := where_clause || ' ' || _key || ' = ' || '''' || _value || '''' || ' AND' ;
    END LOOP ;
    where_clause = TRIM(TRAILING 'AND' FROM where_clause) ;
    FOR _table IN
        SELECT * FROM json_array_elements_text(_tables -> 'tabnames')
    LOOP
        query_string := query_string || ' (SELECT * FROM ' || _table || ' ' || where_clause || ') UNION' ;
        RAISE NOTICE 'Query string now: %', query_string ;
    END LOOP ;
    query_string = TRIM(TRAILING 'UNION' FROM query_string) ;
    RETURN query_string ;
END ;
$$ LANGUAGE plpgsql ;