postgresql - 如何将游标的表名作为函数参数传递？

Question

在 PostgreSQL 9.1 中，我需要将表名作为参数传递给我的函数以在游标中使用 - 然后调用该函数:

select myfunction('tableA');

或:

select myfunction('tableB')

我试过这个:

 create or replace function myfunction(sid text) 
 returns integer as $BODY$
 declare
      bids cursor is select id from sid; --line 4
 begin
      for bid in bids loop
          --some logic
      end loop;
      return 1;
 end;
 $BODY$
 language plpgsql volatile;

我在第 4 行收到错误。我明白了，不允许将表名作为函数参数传递给它允许休息的地方(选择、投影)。

是否有其他方法可以实现这一点？

Answer 1

在 SQL 命令中只能参数化值，不能参数化标识符或关键字。目前它仅适用于 SELECT、INSERT、UPDATE 和 DELETE 命令。详情:

• Error when setting n_distinct using a plpgsql variable

您需要在 plpgsql 中使用 EXECUTE 的动态 SQL。
将其与 FOR 循环的隐式游标相结合，后者通常比显式游标更简单、更快。 Instructions in the manual.

我还建议使用 regclass 参数来安全地传递有效表名。

CREATE OR REPLACE FUNCTION myfunction(_tbl regclass) 
  RETURNS int  AS
$func$
DECLARE
   bid integer;  -- appropriate data type!
BEGIN
   FOR bid IN EXECUTE
      'SELECT id FROM ' || _tbl
   LOOP
      -- statements can use bid
      RAISE NOTICE '%', bid;
   END LOOP;

   RETURN 1;
END
$func$  LANGUAGE plpgsql;

调用:

SELECT myfunction('tableA');  -- careful: case sensitive!

更多解释:

• Table name as a PostgreSQL function parameter

通常，有一个更快的基于集合的解决方案，根本不需要循环。

---

如果您确实需要游标，请声明一个未绑定(bind)游标并使用OPEN FOR EXECUTE 打开它。 :

CREATE OR REPLACE FUNCTION myfunction(_tbl text)  -- could be regclass again
  RETURNS int AS
$func$
DECLARE
   _bids refcursor;
   _rec  record;
BEGIN
   OPEN _bids FOR EXECUTE 'SELECT id FROM ' || quote_ident(_tbl);  -- text must be escaped

   LOOP
      FETCH NEXT FROM _bids INTO _rec;
      EXIT WHEN _rec IS NULL;

      -- some logic
      RAISE NOTICE '%', _rec.id;
   END LOOP;

   RETURN 1;
END
$func$  LANGUAGE plpgsql;

同样的电话。这个密切相关的答案中的更多详细信息:

• Update record of a cursor where the table name is a parameter