Data fetching from a View in Postgresql Using Comma Separated Input

Question

Created a view , my input will be in a comma separated , input contains only the column names of the view .

My input is '"Name","Description"' like this .

I tried like this

Select unnest(string_to_array('"Name","Description"',',')) 
 From  my_test_view

Unfortunately i got result like this

"Name"
"Description"
"Name"
"Description"

this only selecting my column names . I need json output with column values . my input is dynamic

Answer 1

Effectively, what you are trying to do is to dynamically use identifiers on a view for selecting data. You need to write a PL/pgSQL function to do that.

If you trust the input data (i.e. you are certain that the input only contains comma-separated column names) then the solution is very straightforward:

CREATE FUNCTION my_test_view_dynamic(columns text) RETURNS SET OF my_test_view AS $$
BEGIN
    RETURN QUERY EXECUTE format('SELECT %s FROM my_test_view', columns);
END;
$$ LANGUAGE plpgsql STABLE STRICT;

If you are not so sure about the sanity of the input data, check it first:

CREATE FUNCTION my_test_view_dynamic(columns text) RETURNS SET OF my_test_view AS $$
DECLARE
    c text;
    safe_names text[] := '{}'::text[];
BEGIN
    FOREACH c IN ARRAY regexp_split_to_array(columns, ',') LOOP
        safe_names := safe_names || quote_identifier(c);
    END LOOP;
    RETURN QUERY EXECUTE format('SELECT %s FROM my_test_view', concat_ws(safe_names, ','));
END;
$$ LANGUAGE plpgsql STABLE STRICT;

Then call like:

SELECT * FROM my_text_view_dynamic('"Name","Description"');