如何在“EXECUTE format()”中使用 unnest 参数在 plpgsql 中？

Question

我试图在“unnest”内发送一个数组，但在“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