已准备的执行计划的数据类型错误

CREATE OR REPLACE FUNCTION graficar(tabla text, columna text, valor text)
  RETURNS SETOF resultado As
$func$
DECLARE
   _query text;
   limite text := '';
BEGIN
IF columna <> '' THEN
   limite := format(' AND %I = %L', columna, valor); -- properly escaped
END IF;

_query :=
   CASE tabla
   WHEN 'edad' THEN
   $q$WITH ranges AS (
         SELECT concat(ten, '0-', ten, '9') AS range
               ,ten*10 AS r_min, ten*10+9 AS r_max
         FROM   generate_series(1,9) ten)
      SELECT r.range AS nombre, count(p.*)::int AS cuenta
      FROM   ranges       r
      LEFT   JOIN persona p ON p.edad BETWEEN r.r_min AND r.r_max$q$
      || limite || '
      GROUP  BY r.range
      ORDER  BY r.range'

   WHEN 'ingreso' THEN
   $q$WITH ranges AS (
         SELECT concat(ten, '0-', ten*10 + 199, ' mil') AS range
               ,ten*10/1000 AS r_min, (ten*10+199999)/1000 AS r_max
         FROM   generate_series(0,(SELECT max(ingreso)/10000 FROM persona)
                                                             , 20) AS ten)
      SELECT r.range AS nombre, count(p.*)::int AS cuenta
      FROM   ranges r
      LEFT   JOIN persona p ON p.ingreso BETWEEN r.r_min AND r.r_max$q$
      || limite || '
      GROUP  BY r.range
      ORDER  BY r.range'

   ELSE
      format(
   $q$SELECT t.nombre, count(p.*)::int AS cuenta
      FROM   %1$I t
      LEFT   JOIN persona p on p.%1$I = t.nombre$q$ || limite || '
      GROUP  BY t.nombre'
      , tabla)
   END;

RETURN QUERY EXECUTE _query;

END
$func$ LANGUAGE plpgsql;

要点:

• 使用 text 代替 character varying 为了简单起见。

• 使用 concat() 以便于格式化。需要Postgres 9.1+版本。

• 使用 人类可读格式 ! 你发布的字符串几乎无法维护。

• 从开始生成数字 1 对于第一种情况,因为您排除了 0 无论如何,在最后。因此,修剪现在多余的 HAVING 条款

• 这个 assignment operator in plpgsql is := 不 = -这通常是有效的,但这是一个未记录的功能,可能会在未来的版本中消失。

• 正确使用美元报价。

• 使用简单的 RETURN QUERY 代替整体 LOOP 最后构造。

• 不使用 query 作为变量名,它是plpgsql中的一个保留字。已替换 _query 相反

• 为了避免潜在的 类型不匹配 像 described by @Daniel 提供 valor 作为查询中的字符串文字。这是一个罕见的例外!通常情况下,高级方法是通过 USING 子句。但是要提供一系列可能变化的类型,最好的选择是提供一个可以强制为 任何 自动键入。通过这种方式,表达式保持不变 sargable 并且可以使用可能存在于该列的任何索引。

• 通过正确转义避免SQL注入 全部的 标识符和字符串。我正在使用 format() 主要地需要Postgres 9.1+版本。 Details in this related answer on dba.SE.

自从 EXECUTE 应该在每次运行时重新计划,但错误不应该来自计划缓存问题。

因此,除此之外,这个函数中似乎还有两个潜在的错误:

1) 何时 columna<>'' 为false,则动态查询中没有参数,但 执行 尝试使用提交值 USING valor .

2) 何时 columna<>'' 是true,它将此列与类型为的值进行比较 character varying 即使列的类型不能隐式地与该值进行比较。据推测,这需要对文本进行显式转换:

limite = ' where ' || columna || '::text =  $1';

并拥有 valor 参数的类型为 text (或保留 字符变化 但是使用 CAST 语法)。