为什么我会收到"invalid input syntax for type integer"错误？

Question

这是我的存储过程:

CREATE OR REPLACE PROCEDURE delete_events(job_id int, config jsonb)
LANGUAGE plpgsql
AS
$$
DECLARE
l_rec record;
policy_id integer;
deactivate_compression_policy_sql text;
decompress_sql text;
delete_sql text;
activate_compression_policy_sql text;
run_compression_sql text;
BEGIN
FOR l_rec IN SELECT table_schema, TABLE_NAME
FROM information_schema.tables
WHERE TABLE_NAME = 'log_win'
AND table_schema LIKE '%org_%'
LOOP
policy_id := format('SELECT job_id 
FROM timescaledb_information.jobs 
WHERE proc_name = ''policy_retention'' 
AND hypertable_schema = ''%I''  
AND hypertable_name = ''log_win''', l_rec.table_schema);
EXECUTE policy_id;
deactivate_compression_policy_sql := format('SELECT alter_job(%I, scheduled => false)', policy_id);
EXECUTE deactivate_compression_policy_sql;
decompress_sql := format('SELECT decompress_chunk(c, true) FROM show_chunks(''%I.log_win'') c', l_rec.table_schema);
EXECUTE decompress_sql;
delete_sql := format('WITH summary AS (
SELECT time, device_id, ROW_NUMBER() OVER (PARTITION BY device_id
ORDER BY time DESC) AS rank
FROM %I.log_win
JOIN %I.device USING (device_id)
)
DELETE FROM %I.log_win 
USING summary
WHERE summary.rank = 2000 AND log_win.time < summary.time AND summary.device_id = log_win.device_id', l_rec.table_schema, l_rec.table_schema, l_rec.table_schema);
EXECUTE delete_sql;
activate_compression_policy_sql := format('SELECT alter_job(%I, scheduled => true)', policy_id);
EXECUTE activate_compression_policy_sql;
run_compression_sql := format('CALL run_job(%I)', policy_id);
EXECUTE run_compression_sql;
END LOOP;
END
$$;

我的问题是与policy_id。策略id是表压缩策略的id。我正在尝试解压缩表，以便在再次压缩之前修剪数据。当我运行这个程序时，我得到以下错误:

ERROR:  invalid input syntax for type integer: "SELECT job_id 
FROM timescaledb_information.jobs 
WHERE proc_name = 'policy_retention' 
AND hypertable_schema = 'org_3326'  
AND hypertable_name = 'log_win'"
CONTEXT:  PL/pgSQL function delete_events(integer,jsonb) line 17 at assignment
SQL state: 22P02

当我运行select:SELECT job_id FROM timescaledb_information.jobs WHERE proc_name = 'policy_retention' AND hypertable_schema = 'org_3326' AND hypertable_name = 'log_win'时，我得到1092，它甚至说值是integer。我不明白这里有什么问题?

Answer 1

format()不执行SQL语句，它只是构造一个字符串。不能将字符串赋值给integer变量(除非它可以被强制转换为数字)。要执行查询，必须使用EXECUTE。

所以正确的顺序应该是:

EXECUTE format('SELECT job_id 
FROM timescaledb_information.jobs 
WHERE proc_name = ''policy_retention'' 
AND hypertable_schema = %L  
AND hypertable_name = ''log_win''', l_rec.table_schema)
INTO policy_id;

注意，必须使用%L格式来构造字符串字面值。你的代码容易受到SQL注入的攻击。