问题描述
我正在尝试将动态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,无法识别记录数据类型中的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!