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

qc6wkl3g  于 2023-10-18  发布在  PostgreSQL
关注(0)|答案(3)|浏览(728)
  1. CREATE OR REPLACE FUNCTION display_columns_data(table_name varchar, columns_to_display varchar[])
  2. RETURNS TABLE (result_row json) AS
  3. $$
  4. BEGIN
  5. RETURN QUERY EXECUTE
  6. 'SELECT ' || array_to_string(columns_to_display, ',') || ' FROM ' || table_name;
  7. END;
  8. $$
  9. LANGUAGE plpgsql;
  1. SELECT * FROM display_columns_data('employees', ARRAY['first_name', 'last_name']);
  1. Detail: Returned type character varying(20) does not match expected type json in column 1.
  2. Where: SQL statement "SELECT first_name,last_name FROM employees"
  3. PL/pgSQL function display_columns_data(character varying,character varying\[\]) line 3 at RETURN QUERY
zc0qhyus

zc0qhyus1#

该方法中的SQL查询返回一个结果集,其中包含类型为charactervarying(20)或字符串的列,这就是您遇到问题的原因。函数display_columns_data的目的是返回一个表,表中只有一个json类型的列。这个问题是由定义的返回类型和实际结果集之间的差异引起的。
尝试使用此代码进行函数定义;

  1. CREATE OR REPLACE FUNCTION display_columns_data(table_name varchar, columns_to_display varchar[])
  2. RETURNS TABLE (result_row record) AS
  3. $$
  4. BEGIN
  5. RETURN QUERY EXECUTE
  6. 'SELECT ' || array_to_string(columns_to_display, ',') || ' FROM ' || table_name;
  7. END;
  8. $$
  9. LANGUAGE plpgsql;

上述代码中的RETURNS TABLE子句指定该函数应生成一个表,该表具有一个名为result_row的单列,其类型为record。这将匹配动态SQL查询生成的结果集,该结果集可能具有不同类型的列,具体取决于输入columns_to_display。
希望它能起作用:)

lh80um4z

lh80um4z2#

如果你想返回一个JSON对象,你必须在SELECT语句中将列转换为JSON;

  1. CREATE OR REPLACE FUNCTION display_columns_data(table_name varchar, columns_to_display varchar[])
  2. RETURNS TABLE (result_row json) AS
  3. $$
  4. BEGIN
  5. RETURN QUERY EXECUTE
  6. 'SELECT json_build_object(' || array_to_string(columns_to_display, ',') || ') FROM ' || table_name;
  7. END;
  8. $$
  9. LANGUAGE plpgsql;
vm0i2vca

vm0i2vca3#

这将返回一组JSON值,每个值用给定的列名 Package 一行,同时将所述列名保留为键名。安全

  1. CREATE OR REPLACE FUNCTION display_columns_data(table_name text, columns_to_display text[])
  2. RETURNS TABLE (result json)
  3. LANGUAGE plpgsql AS
  4. $func$
  5. BEGIN
  6. RETURN QUERY EXECUTE format (
  7. 'SELECT to_json((SELECT t FROM (SELECT %s) t)) FROM %I'
  8. , (SELECT string_agg(quote_ident(col), ', ') FROM unnest(columns_to_display) col) -- !
  9. , table_name
  10. );
  11. END
  12. $func$;

请注意我是如何解开列名数组并分别通过quote_ident()运行它们以关闭SQL注入的大门的,这是敞开的。列名必须不加引号且区分大小写。
请参阅:

  • 返回同一行的多个列作为JSON对象数组
  • 如何在Postgres函数中使用文本输入作为列名?
展开查看全部

相关问题