plpgsql函数问题

Question

我有以下plpgsql过程；

DECLARE 
     _r record;
     point varchar[] := '{}';
     i int := 0;

BEGIN



FOR _r IN EXECUTE ' SELECT a.'|| quote_ident(column) || ' AS point,
       FROM ' || quote_ident (table) ||' AS a'
LOOP

       point[i] = _r;
       i = i+1;

END LOOP;

RETURN 'OK';
END;

它的主要目标是遍历表并将行的每个值存储在数组中。我仍然是plpgsql的新手。谁能指出这是一个错误，因为它给了我以下错误；

​

Answer 1

这是完整的语法(请注意，我将参数column重命名为col_name，因为column是保留字。table也是如此)

create or replace function foo(col_name text, table_name text)
  returns text
as
$body$

DECLARE 
     _r record;
     point character varying[] := '{}';
     i int := 0;

BEGIN
    FOR _r IN EXECUTE 'SELECT a.'|| quote_ident(col_name) || ' AS pt, FROM ' || quote_ident (table_name) ||' AS a'
    loop
     point[i] = _r;
     i = i+1;
   END LOOP;

   RETURN 'OK';
END;
$body$
language plpgsql;

尽管老实说:我失败了，所以看看你在这里想要实现什么。

Answer 2

@a_horse修复了您失败的尝试中的大多数严重问题。

但是，任何人都不应该使用这个。下面的分步说明应该能用现代的PostgreSQL实现一个合理的实现。

阶段1:删除错误和恶作剧

• 删除SELECT列表后的逗号以修复语法错误。
• 您的数组以0开头，而默认值是以1开头。只有在你需要这样做的时候才这样做。如果您使用array_upper()等人操作，会导致意想不到的结果。首先将instead.
• Change RETURN类型转换为varchar[]，以返回汇编的数组，并使此演示有用。

到目前为止我们所拥有的：

CREATE OR REPLACE FUNCTION foo(tbl varchar, col varchar)
  RETURNS varchar[] LANGUAGE plpgsql AS
$BODY$
DECLARE 
   _r record;
   points varchar[] := '{}';
   i int := 0;
BEGIN
   FOR _r IN
      EXECUTE 'SELECT a.'|| quote_ident(col) || ' AS pt
      FROM ' || quote_ident (tbl) ||' AS a'
   LOOP
      i = i + 1;        -- reversed order to make array start with 1
      points[i] = _r;
   END LOOP;

   RETURN points;
END;
$BODY$;

阶段2:移除cruft，使其有用

• 为简单起见，使用text而不是character varying / varchar。
• 您正在选择单个列，但是使用类型为record的变量。这样一来，整个记录都被强制转换为text，其中包括圆括号。很难说得通。请改用text变量。如果显式强制转换为text (::text)，则适用于任何列。初始化变量point没有意义，任何类型都可以转换为text.
• There。在这里，它可以以NULL开头。在这种情况下，

中的

• 表和列别名在EXECUTE中没有任何用处。动态执行的function.
• It's有它自己的作用域！.
• 在plpgsql中，在final END之后不需要分号(;)更简单，只需用||将每个值追加到数组中。

几乎是正常的：

CREATE OR REPLACE FUNCTION foo1(tbl text, col text)
  RETURNS text[] LANGUAGE plpgsql AS
$func$
DECLARE 
   point  text;
   points text[];
BEGIN
   FOR point IN
      EXECUTE 'SELECT '|| quote_ident(col) || '::text FROM ' || quote_ident(tbl)
   LOOP
      points = points || point;
   END LOOP;

   RETURN points;
END
$func$;

阶段3:让它在现代PL/pgSQL中大放异彩

• 如果将表名作为text传递，则会造成不明确的情况。您可以使用format()或quote_ident()很好地防止SQLi，但对于search_path之外的表，这将失败。

然后，您需要添加模式限定，这会创建一个不明确的值。'x.y‘可以代表表名"x.y“或模式限定的表名"x"."y”。您不能传递"x"."y“，因为它将被转义为”“x”“.”“y”“。您需要为模式名使用一个额外的参数，或者在强制转换为时，regclass类型的一个参数会根据需要自动加引号，这在这里是很好的解决方案。

• 新的format()比多个(甚至单个) quote_ident()调用更简单。

• 您没有指定任何order。在没有ORDER BY的情况下，SELECT以任意顺序返回行。这可能看起来很稳定，因为只要底层表不变，结果通常是可重现的。但这是100%不可靠的。您可能希望添加一些根本不需要循环的ORDER BY.

• Finally，。使用带有.

• Use an OUT SELECT参数的普通参数进一步简化代码

正确的解决方案：

CREATE OR REPLACE FUNCTION f_arr(tbl regclass, col text, OUT arr text[])
 LANGUAGE plpgsql AS
$func$
BEGIN

EXECUTE format('SELECT ARRAY(SELECT %I::text FROM %s ORDER BY 1)', col, tbl)
INTO arr;

END
$func$;

呼叫：

SELECT f_arr('myschema.mytbl', 'mycol');