postgresql - 在 SELECT 生成的行上循环 - 循环变量不存在？

Question

函数创建的很好，但是当我尝试执行它时，我得到了这个错误:

ERROR: relation "column1" does not exist
SQL state: 42P01
Context: SQL statement "ALTER TABLE COLUMN1 ADD COLUMN locationZM geography (POINTZM, 4326)"
PL/pgSQL function addlocationzm() line 6 at SQL statement

代码:

CREATE OR REPLACE FUNCTION addlocationZM() 
RETURNS void AS
$$
    DECLARE
        COLUMN1 RECORD;
    BEGIN
        FOR COLUMN1 IN SELECT f_table_name FROM *schema*.geography_columns WHERE type LIKE 'Point%' LOOP
            ALTER TABLE COLUMN1 ADD COLUMN locationZM geography (POINTZM, 4326); 
        END LOOP;
    END;
$$
LANGUAGE 'plpgsql';

SELECT addlocationZM()

我可能只是太笨了，但我已经在这方面工作了一段时间了，但我就是无法理解。单独执行的 SELECT f_table_name ... 语句返回单列的 58 行，每行都是我的架构中的一个表的名称。这样做的想法是在 SELECT 提取的每个表中创建一个新列，类型为 PointZM。

Answer 1

函数会像这样工作:

CREATE OR REPLACE FUNCTION addlocationZM() 
  RETURNS void AS
$func$
DECLARE
   _tbl text;
BEGIN
   FOR _tbl IN
      SELECT f_table_name FROM myschema.geography_columns WHERE type LIKE 'Point%'
   LOOP
      EXECUTE
      format('ALTER TABLE %I ADD COLUMN location_zm geography(POINTZM, 4326)', _tbl); 
   END LOOP;
END
$func$  LANGUAGE plpgsql;

请注意我是如何使用一个简单的 text 变量来简化问题的。您不需要以 record 开头。

如果是一次性操作，请使用DO 命令而不是创建函数:

DO
$do$
BEGIN
   EXECUTE (
      SELECT string_agg(
                format(
                   'ALTER TABLE %I ADD COLUMN location_zm geography(POINTZM, 4326);'
                  , f_table_name)
              , E'\n')
      FROM   myschema.geography_columns
      WHERE  type LIKE 'Point%'
      );
END
$do$;

这是为单个 EXECUTE 连接一个由所有命令组成的字符串(用 ; 分隔)。

或者，尤其是当您不熟悉 plpgsql 和动态 SQL 时，只需生成命令，复制/粘贴结果并作为第二步执行:

SELECT 'ALTER TABLE '
     || quote_ident(f_table_name)
     || ' ADD COLUMN locationZM geography(POINTZM, 4326);'
FROM   myschema.geography_columns
WHERE  type LIKE 'Point%';

(这次演示 quote_ident()。)

相关:

• Table name as a PostgreSQL function parameter

旁白:不带引号的 CaMeL 大小写标识符，如 locationZM 或您的函数名称 addlocationZM 可能不是一个好主意:

• Are PostgreSQL column names case-sensitive?