Postgres函数填充记录集和返回的ID

Question

我正在尝试INSERT一条记录，返回ID，然后将ID传递给其他表并插入其他数据。要插入的数据采用JSON格式。

我正在使用一个函数调用来插入数据，当create语句工作时，我在执行它的过程中做了一些错误的事情(所以要么a)我没有正确地编写语句，要么b)我没有适当地传入数据)。

我得到的错误是这样的：

ERROR: INSERT has more target columns than expressions
SQL state: 42601
Context: PL/pgSQL function insert_to_tables(jsonb,jsonb,jsonb) line 3 at SQL statement

以下是我的表格外观：

CREATE TABLE main_data(
id SERIAL PRIMARY KEY,
field_1 TEXT,
some_time DATE
);
CREATE TABLE locale_data(
locale_id SERIAL PRIMARY KEY,
city TEXT,
state TEXT,
address TEXT,
main_data_id INTEGER REFERENCES main_data(id)
);
CREATE TABLE demographic_data(
demographic_id SERIAL PRIMARY KEY,
age INT,
ethnicity TEXT,
main_data_id INTEGER REFERENCES main_data(id)
);

我有一个这样的功能来处理数据输入和解析：

CREATE OR REPLACE FUNCTION insert_to_tables (
main_data_fields JSONB,
locale_data_fields JSONB,
demographic_data_fields JSONB,
OUT new_user_id INTEGER
)
RETURNS integer AS $$
BEGIN
WITH ins AS (
INSERT INTO main_data SELECT * FROM jsonb_populate_recordset(NULL::main_data, $1::jsonb)
RETURNING id
)
INSERT INTO locale_data(city, state, address, main_data_id)
SELECT i.id AS main_data_id, jsonb_populate_recordset(NULL::locale_data, $2::jsonb)
FROM  ins i;
INSERT INTO demographic_data(age, ethnicity)
SELECT i.id AS main_data_id, jsonb_populate_recordset(NULL::demographic_data, $3::jsonb)
FROM  ins i;
END;
$$ LANGUAGE plpgsql;

为了插入数据，我调用这样的函数：

select insert_to_tables(
'{"field_1": "http://www.google.com", "some_time": "09-02-2019"}',
'[{"city": "a city", "address": "123 fake road", "state": "CA"}, {"city": "little city", "address": "456 noname road", "state": "WA"}]',
'[{"age": 45, "ethnicity": "Asian"}, {"age": "45", "ethnicity": "Egyptian"}]'
);

我期望的输出应该是填充有1行的main_data表、填充有2行的locale_data表和填充有2行将的demographic_data表。

locale和demographic表中的每一行都属于并应该引用main_data表中的行，如下所示：

id  |         field1_1        | some_time  |
----+-------------------------+------------+
1  |  http://www.google.com  |  09-02-2019

locale_id  |    city        |  state  |     address      | main_data_id
-----------+----------------+---------+------------------+---------+
1         |     a city     |   CA    | 123 fake road    |   1
2         |   little city  |   WA    | 456 noname road  |   1
locale_id  |    age      |    ethnicity   |  main_data_id
-----------+-------------+----------------+------------------+
1         |     45      |   Asian        |    1
2         |     45      |   Egyptian     |    1 

我猜我的第二组INSERT INTO语句写错了，但我不知道在从另一个操作返回ID后如何处理JSON的数据插入。

Answer 1

主要问题-使用jsonb_populate_recordset()时，必须确保json值的数量与表列的数量完全相同。您不希望填充表的id，因为它们是串行的，所以您必须指示列列表和适当的选择列表。此外，函数jsonb_populate_recordset()适用于json数组，因此第一个参数必须是一个数组，就像另外两个一样。

公共表表达式(带命令)是一个带有多个子查询的单个查询，因此函数语言应该是SQL，最终查询应该返回插入main_data的行的id。

CREATE OR REPLACE FUNCTION insert_to_tables (
main_data_fields JSONB,
locale_data_fields JSONB,
demographic_data_fields JSONB
)
RETURNS integer AS $$
WITH ins AS (
INSERT INTO main_data (field_1, some_time)
SELECT field_1, some_time
FROM jsonb_populate_recordset(NULL::main_data, $1::jsonb)
RETURNING id
),
ins_locale AS (
INSERT INTO locale_data (city, state, address, main_data_id)
SELECT city, state, address, ins.id
FROM ins, jsonb_populate_recordset(NULL::locale_data, $2::jsonb)
),
ins_demographic AS (
INSERT INTO demographic_data (age, ethnicity, main_data_id)
SELECT age, ethnicity, ins.id
FROM ins, jsonb_populate_recordset(NULL::demographic_data, $3::jsonb)
)
SELECT id
FROM ins;
$$ LANGUAGE sql;
SELECT insert_to_tables (
'[{"field_1": "http://www.google.com", "some_time": "09-02-2019"}]',
'[{"city": "a city", "address": "123 fake road", "state": "CA"}, {"city": "little city", "address": "456 noname road", "state": "WA"}]',
'[{"age": 45, "ethnicity": "Asian"}, {"age": "45", "ethnicity": "Egyptian"}]'
);

请参阅此处的完整示例。