如何在 plpgsql 的"EXECUTE format()"中使用 unnest 参数？

Question

我试图在一个"unnest"；但是在a"；EXECUTE format(("，我该怎么做？

表

CREATE TABLE IF NOT EXISTS table_xvx(
row_id SERIAL NOT NULL PRIMARY KEY,
open FLOAT(36),
high FLOAT(36),
low FLOAT(36),
close FLOAT(36),
volume FLOAT(36),
capital FLOAT(36),
transactions FLOAT(36)
)

类型

CREATE TYPE type_ohlcvct AS(
type_open float,
type_high float,
type_low float,
type_close float,
type_volume float,
type_capital float,
type_transactions float
);

功能

CREATE OR REPLACE FUNCTION insert_data_ohlcvct(table_name_ varchar(70) , data_list type_ohlcvct[]) 
RETURNS VOID AS
$$
DECLARE
error_message varchar;
BEGIN
EXECUTE format(
'INSERT INTO %I(open,high,low,close,volume,capital,transactions)
SELECT * FROM unnest(%s)',table_name_ , data_list); 
END;
$$ LANGUAGE plpgsql;

调用的方法

SELECT insert_data_ohlcvct('table_xvx' ,'{"(10,10,10,10,10,10,10)","(10,10,10,10,10,10,10)"}'::type_ohlcvct[]);
SELECT insert_data_ohlcvct('table_xvx' , (ARRAY['(10,10,10,10,10,10,10)','(10,10,10,10,10,10,10)'])::type_ohlcvct[]);
SELECT insert_data_ohlcvct('table_xvx' , ARRAY['(10,10,10,10,10,10,10)'::type_ohlcvct,'(10,10,10,10,10,10,10)']);

错误总是相同的

ERROR:  syntax error at or near "{"
LINE 2:   SELECT * FROM unnest({"(10,10,10,10,10,10,10)","(10,10,10,...
^
QUERY:  INSERT INTO table_xvx(open,high,low,close,volume,capital,transactions)
SELECT * FROM unnest({"(10,10,10,10,10,10,10)","(10,10,10,10,10,10,10)"})
CONTEXT:  PL/pgSQL function insert_data_ohlcvct(character varying,type_ohlcvct[]) line 5 at EXECUTE
SQL state: 42601

Answer 1

不要将参数作为字符串传递给动态SQL-将它们作为参数传递：

EXECUTE format(
'INSERT INTO %I(open,high,low,close,volume,capital,transactions)
SELECT * FROM unnest($1)',table_name_)
USING data_list;  --<< passes the value to the $1 placeholder