如何在postgres查询中将regclass名称与字符串连接起来

Question

我想使用这个函数在分区表上创建索引。Select query给出了test.t的所有分区表，然后迭代它的名称并创建索引。

CREATE OR REPLACE FUNCTION test.create_index_test() RETURNS void AS
$BODY$
DECLARE
    f record;
BEGIN
    FOR f IN
        SELECT inhrelid::regclass-- optionally cast to text
        FROM   pg_catalog.pg_inherits
        WHERE  inhparent = 'test.t'::regclass
    loop
    CREATE INDEX concat(f::text,'_geom_index') ON f USING gist (geom);

        -- can do some processing here
    END LOOP;
END;
$BODY$
LANGUAGE plpgsql;

但是这给了我concat函数中的错误

ERROR:  syntax error at or near "("
LINE 11:  CREATE INDEX concat(f,'_geom_index') ON f USING gist (geom)...

Answer 1

DDL语句不支持参数化-因此您不能在其中使用变量。

您需要使用动态SQL - EXECUTE语句

CREATE OR REPLACE FUNCTION test.create_index_test() RETURNS void AS
$BODY$
DECLARE
  r record;
BEGIN
  FOR r IN
     SELECT inhrelid::regclass AS fullpath, c.relname
       FROM pg_catalog.pg_inherits
            JOIN pg_class c ON c.oid = inhrelid
      WHERE  inhparent = 'test.t'::regclass
  LOOP
    EXECUTE FORMAT('CREATE INDEX %I ON %s USING gist(geom)', 
                   r.relname || '_geom_index',
                   r.fullpath);

    -- the index name is optional in postgres, so it can be simplified
    -- EXECUTE FORMAT('CREATE INDEX ON %s USING gist(geom)',
    --               r.fullpath);
    -- can do some processing here
  END LOOP;
END;
$BODY$
LANGUAGE plpgsql;