PostgreSQL，无法识别记录数据类型中的列

问题描述

我正在尝试将动态SQL与Postgresql一起使用.我一直试图在另一个 EXECUTE 语句中使用 db_row RECORD中的 id 字段.在记录变量中设置了字段 id ，但仍然出现错误:

I'm trying use dynamic SQL with Postgresql. I'm stuck with trying to use id field from db_row RECORD in another EXECUTE statement. Field id is set in record variable but I still get error:

ERROR:  could not identify column "id" in record data type
LINE 1: SELECT $1.id FROM audit.portfolio WHERE alter_type = 'INSERT...
               ^
QUERY:  SELECT $1.id FROM audit.portfolio WHERE alter_type = 'INSERT' AND id = $1.id
CONTEXT:  PL/pgSQL function inline_code_block line 14 at EXECUTE

这是我的数据和测试功能:

This is my data and test function:

CREATE TABLE IF NOT EXISTS public.portfolio
(
  id   INTEGER NOT NULL,
  name CHARACTER VARYING,
  CONSTRAINT portfolio_pkey PRIMARY KEY (id)
)
WITH (OIDS = FALSE);

CREATE TABLE IF NOT EXISTS audit.portfolio
(
  alter_type TEXT,
  id         INTEGER NOT NULL,
  name       CHARACTER VARYING,
  CONSTRAINT portfolio_pkey PRIMARY KEY (id)
)
WITH (OIDS = FALSE
);

INSERT INTO public.portfolio (id, name) VALUES (1, 'NAME 1'), (2, 'NAME 2');

-- temp function
DO $$
DECLARE
  db_row RECORD;
  pk_val BIGINT;

BEGIN

  FOR db_row IN EXECUTE format(
      'SELECT *, 100 AS created_by_id, %L::TIMESTAMP AS creation_time FROM public.%I ORDER BY %I',
      '2014-01-01 00:00:00', 'portfolio', 'id', 'id')
  LOOP
    RAISE NOTICE 'db_row.id: %', db_row.id;
    EXECUTE
    format(
        'SELECT $1.%I FROM audit.%I WHERE alter_type = %L AND %I = $1.%I',
        'id', -- this will be dynamic and change when table change
        'portfolio', -- this will be dynamic and change when table change
        'INSERT',
        'id', -- this will be dynamic and change when table change
        'id' -- this will be dynamic and change when table change
    )
    INTO pk_val
    USING db_row;
    RAISE NOTICE 'pk_val: %', pk_val;
  END LOOP;
END$$;

似乎postgres EXECUTE ...使用db_row 不能看到 db_row 的完整结构，就像 porfolio 表中的行一样.

It seems like postgres EXECUTE ... USING db_row does not see full structure of db_row that is like row from porfolio table.

我的最终目标是创建将采用任何现有表名的函数做到这一点:

My final goal is to create function that will take any existing table namedo this:

EXECUTE
    format(
        'SELECT $1.%I FROM audit.%I WHERE alter_type = %L AND %I = $1.%I',
        'primary_key_col',
        'table_name',
        'INSERT',
        'primary_key_col',
        'primary_key_col'
    )
    INTO pk_val
    USING db_row

如何告诉postgres在此 EXECUTE 语句中使用 db_row.id 值?

How to tell postgres to use db_row.id value in this EXECUTE statement?

推荐答案

问题是您正在将类型为 record 的参数( db_row )传递给SQL语句，但是对于SQL引擎，该值没有已知的结构.您甚至无法将其强制转换为表类型.

The problem is that you are passing a parameter (db_row) of type record to an SQL statement, but such a value has no known structure for the SQL engine. You cannot even cast it to the table type.

我可以想到一个丑陋的解决方法，将记录转换为其文本表示形式(可与对类型输出函数的调用一起使用)，然后将测试转换为所需的表类型.

One ugly workaround I can think of it to cast the record to its text representation (which works with a call to the type output function) and then cast the test to the desired table type.

这是示例代码，阐明了我的意思:

This is sample code that illustrates what I mean:

DO $$
DECLARE
   r record;
   n name;
BEGIN
   /* find all tables with a column "oid" */
   FOR r IN
      SELECT t.*
         FROM pg_class t
              JOIN pg_attribute a ON a.attrelid = t.oid
         WHERE a.attname = 'oid'
   LOOP
      /* get the table name */
      EXECUTE format(
                 'SELECT ($1::text::%s).relname',
                 'pg_class'
              ) INTO n USING r;
      RAISE NOTICE 'Table name: %', n;
   END LOOP;
END;
$$;

这篇关于PostgreSQL，无法识别记录数据类型中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！