Question 252242 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    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 🙂

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search