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 :)

Answer 2

If you would like to return a JSON object you have to cast the columns to JSON within your SELECT statement;

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 json_build_object(' || array_to_string(columns_to_display, ',') || ') FROM ' || table_name;
END;
$$
LANGUAGE plpgsql;

Answer 3

This returns a set of JSON values, each wrapping a row with the given column names, while preserving said column names as key names. And safely:

CREATE OR REPLACE FUNCTION display_columns_data(table_name text, columns_to_display text[])
  RETURNS TABLE (result json)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format (
      'SELECT to_json((SELECT t FROM (SELECT %s) t)) FROM %I'
    , (SELECT string_agg(quote_ident(col), ', ') FROM unnest(columns_to_display) col)  -- !
    , table_name
   );
END
$func$;

Note how I unnest the array of column names and run them through quote_ident() individually to shut the door for SQL injection, which was wide open. Column names have to be passed unquoted and case-sensitive.

See:

• Return multiple columns of the same row as JSON array of objects
• How to use text input as column name(s) in a Postgres function?