SQL Error [42804]: ERROR: structure of query does not match function result type

Question

CREATE OR REPLACE FUNCTION display_columns_data(table_name varchar, columns_to_display varchar[])
RETURNS TABLE (result_row json) AS
$$
BEGIN
    RETURN QUERY EXECUTE
    'SELECT ' || array_to_string(columns_to_display, ',') || ' FROM ' || table_name;
END;
$$
LANGUAGE plpgsql;

SELECT * FROM display_columns_data('employees', ARRAY['first_name', 'last_name']);

Detail: Returned type character varying(20) does not match expected type json in column 1.  
Where: SQL statement "SELECT first_name,last_name FROM employees"
PL/pgSQL function display_columns_data(character varying,character varying\[\]) line 3 at RETURN QUERY

Answer 1

The SQL query inside the method is returning a result set containing columns of type character varying(20), or strings, which is why you're getting the issue. The function display_columns_data is designed to return a table with a single column of type json. The issue is brought on by a discrepancy between the defined return type and the actual result set.

Try this code for your function definition;

CREATE OR REPLACE FUNCTION display_columns_data(table_name varchar, columns_to_display varchar[])
RETURNS TABLE (result_row record) AS
$$
BEGIN
    RETURN QUERY EXECUTE
    'SELECT ' || array_to_string(columns_to_display, ',') || ' FROM ' || table_name;
END;
$$
LANGUAGE plpgsql;

The RETURNS TABLE clause in the above code specifies that the function should produce a table with a single column named result_row having type record .This will match the result set produced by the dynamic SQL query, which may have columns of different types depending on the input columns_to_display.

Hope it works :)