引用ROWTYPE表中的列

Question

在PostgreSQL中，我希望通过引用列号来动态访问ROWTYPE中的列。

例如，我想通过r.column(59)而不是r.columnname来访问第59列。这个是可能的吗？

这样做的目的是希望循环遍历许多列，而不是对所有列进行硬编码。

例如：

r data.stageing_table%rowtype;

FOR r IN SELECT * FROM data.stage_table LOOP
   INSERT INTO TABLE XXX
   .....
   VALUES(r.column(8))

END LOOP

是否可以访问我的行类型结果集中的列？

Answer 1

AFAIK你不能，但你可以做一些可能足够接近的事情。您可以获取列在关系中的位置，并将其作为该数字的别名(以非数字字符为前缀)。相反，您必须将变量声明为record。

pg_attribute系统表示例：

DO $$
DECLARE
  l_row record;
  l_query text := $q$ SELECT %s
                        FROM pg_attribute
                       WHERE attrelid = 'pg_attribute'::regclass
                  $q$;
BEGIN
  FOR l_row IN EXECUTE format(l_query, --below is value that goes into %s placeholder
                              (SELECT string_agg(concat(attname, ' AS c', attnum), ', ')
                                 FROM pg_attribute
                                WHERE attrelid = 'pg_attribute'::regclass
                                  AND attnum > 0)
                             )
  LOOP
    RAISE NOTICE 'Attname: % is column number %', l_row.c2, l_row.c6;
  END LOOP;
END;
$$;

实际执行的查询是：

SELECT attrelid AS c1, attname AS c2, atttypid AS c3, attstattarget AS c4,
       attlen AS c5, attnum AS c6, attndims AS c7, attcacheoff AS c8,
       atttypmod AS c9, attbyval AS c10, attstorage AS c11, attalign AS c12,
       attnotnull AS c13, atthasdef AS c14, attidentity AS c15, attisdropped AS c16,
       attislocal AS c17, attinhcount AS c18, attcollation AS c19, attacl AS c20,
       attoptions AS c21, attfdwoptions AS c22
  FROM pg_attribute
 WHERE attrelid = 'pg_attribute'::regclass

您可以从pg_attribute表中获得列的总数，请记住，那些attnum <0的列是隐藏的系统列，所以不要计算它们。我想您可以只使用max(attnum)或l_columns := array_agg(concat(attname, ' AS c', attnum), ', ');，然后使用array_length(l_columns,1)作为count，使用array_to_string(l_columns,', ')来获取动态SQL的列列表。