在Postgres中从动态查询插入 [英] Insert from Dynamic Query in Postgres

问题描述

关于参考解决方案，我已经在我以前的 post 导致了另外一种情况。
尝试插入到我的目标表时（如下所示）。

With reference solution I've posted in my previous post resulted in one more situation. While trying to insert into my destination table(schema as below).

-- Table: normalized_transaction

-- DROP TABLE normalized_transaction;

CREATE TABLE normalized_transaction
(
  transaction_id uuid,
  file_id uuid,
  account_number character varying(40),
  currency character varying(3),
  trade_date date,
  value_date date,
  narration character varying(200),
  amount numeric,
  mesitis_account_number character varying(50),
  tag character varying(255),
  supporting_file_id uuid,
  supporting_record_id uuid,
  status integer DEFAULT 0,
  source_type integer,
  record_index integer DEFAULT 0
)

使用类似

INSERT INTO normalized_transaction(account_number, currency, trade_date)
select gen_Test('english');
fetch all in english;

结果出错：

ERROR:  INSERT has more target columns than expressions
LINE 2: ...NSERT INTO normalized_transaction(account_number, currency, ...
                                                             ^
********** Error **********

ERROR: INSERT has more target columns than expressions
SQL state: 42601
Character: 53



select gen_Test('english');
    fetch all in english;

以上查询的参考输出：

将结果从表中插入的合适方法是什么。

What is appropriate way to insert the result from this into table.

推荐答案

您可以这样尝试：

INSERT INTO normalized_transaction(account_number, currency, trade_date)
SELECT foo.*
FROM gen_Test('english') as foo;

这篇关于在Postgres中从动态查询插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！