SQL 错误 [42804]：错误：查询结构与函数结果类型不匹配-解网

问：

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

PostgreSQL 参数传递 plpgsql set-returning-functions

该方法中的 SQL 查询返回一个结果集，其中包含 character varying（20）或字符串类型的列，这就是您遇到问题的原因。函数display_columns_data旨在返回具有 json 类型的单列的表。该问题是由定义的返回类型与实际结果集之间的差异引起的。

请尝试将此代码用于函数定义;

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;

上面代码中的 RETURNS TABLE 子句指定该函数应生成一个表，其中包含一个名为 result_row 的列，具有 record 类型。这将与动态 SQL 查询生成的结果集匹配，该查询可能具有不同类型的列，具体取决于输入columns_to_display。

希望它能:)

0赞 Nnaemeka Daniel John 9/25/2023 #2

如果要返回 JSON 对象，则必须在语句中将列强制转换为 JSON;SELECT

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;

0赞 Erwin Brandstetter 9/27/2023 #3

这将返回一组 JSON 值，每个值用给定的列名包装一行，同时将所述列名保留为键名。并且安全地：

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$;

请注意我如何取消列名数组并单独运行它们以关闭 SQL 注入的大门，这是敞开的。列名必须不带引号且区分大小写。quote_ident()

看：

上一个：在 Flutter 中将参数传递给导航窗口

下一个：OpenModelica中的参数分配问题：某些参数未正确分配

SQL 错误 [42804]：错误：查询结构与函数结果类型不匹配

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

评论