sql - 如何在 PostgreSQL 中通过数组正确创建多个条目?
问题描述
在PostgreSQL数据库中,我有如下所示的表:
| question_id | question_text | widget | required | position |
|-------------|---------------|--------|----------|----------|
| int | text | int | boolean | int |
调用的第二个表factors_questions_relationship
如下所示:
| factor_id | question_id |
|-------------|---------------|
| int | text |
我正在尝试创建将创建多行并返回新创建条目的 id 数组的函数。如何正确地制作这样的功能?
CREATE OR REPLACE FUNCTION factorio(
FACTOR_IDENTIFIER INT,
TEXT_ARR VARCHAR[],
WIDGET_ARR INT[],
REQUIRED_ARR BOOLEAN[],
POSITION_ARR INT[]
) RETURNS SETOF INT AS $$
BEGIN
RETURN QUERY
WITH RESULT_SET AS (
INSERT INTO QUESTIONS (TEXT, WIDGET, REQUIRED, POSITION)
SELECT
UNNEST(ARRAY[TEXT_ARR]) AS TEXT,
UNNEST(ARRAY[WIDGET_ARR]) AS WIDGET,
UNNEST(ARRAY[REQUIRED_ARR]) AS REQUIRED,
UNNEST(ARRAY[POSITION_ARR]) AS POSITION
RETURNING ID
)
--
INSERT INTO factors_questions_relationship (FACTOR_ID, QUESTION_ID)
SELECT FACTOR_IDENTIFIER FACTOR_ID, QUESTION_ID FROM UNNEST(ARRAY[array_agg(SELECT ID FROM RESULT_SET)]) QUESTION_ID
--
SELECT ID FROM RESULT_SET;
END;
$$ LANGUAGE plpgsql;
解决方案
您可以简单地将它们取消嵌套在列中
select
unnest(array['quick','brown','fox']) as question_text,
unnest(array[1,2,3]) as widget_id
而将它们放在FROM
子句中,将导致笛卡尔积:
select question_text, widget_id
from
unnest(array['quick','brown','fox']) as question_text,
unnest(array[1,2,3]) as widget_id
输出:
要获取从新插入的记录生成的 Id,请使用return query
+returning id
组合。样品测试:
create table z
(
id int generated by default as identity primary key,
question_text text
);
create or replace function insert_multiple_test()
returns table (someid int) as
$$
begin
return query
with resulting_rows as
(
insert into z(question_text) values
('hello'),
('你好'),
('hola')
returning id
)
select id from resulting_rows;
end;
$$ language 'plpgsql';
select * from insert_multiple_test();
输出:
这是 SETOF:
create table z(id int generated by default as identity primary key, question_text text);
create or replace function insert_multiple_test()
returns setof int
as
$$
begin
return query
with resulting_rows as
(
insert into z(question_text) values
('hello'),
('你好'),
('hola')
returning id
)
select id from resulting_rows;
end;
$$ language 'plpgsql';
select x.the_id from insert_multiple_test() as x(the_id);
输出:
如果您不需要运行多个查询,则可以使用LANGUAGE 'sql'
,它更简单:
create table z
(
id int generated by default as identity primary key,
question_text text
);
create or replace function insert_multiple_test()
returns setof int as
$$
insert into z(question_text) values
('hello'),
('你好'),
('hola')
returning id;
$$ language 'sql';
select x.id_here from insert_multiple_test() as x(id_here);
输出:
推荐阅读
- javascript - 如何在 react native 中将值从 promise 传递给组件 prop?
- c# - 从 .net core 2.2 迁移到 .net core 3.0 后打破视图
- java - Hibernate 注释:具有共享复合键属性的多对多
- php - 如何在 yii2 中返回数组值?
- dotnetnuke - DNN 站点在 dot net nuke 版本中运行缓慢 - 9.4.1
- file - 新创建的 Flutter App 的文件夹和文件说明?
- python - 根据多个条件过滤行
- prometheus-alertmanager - 进程使用过多 CPU 时,prometheus 中带有警报规则的进程导出器
- javascript - /static/ 的位置在设置 app.route(/something/) 时更改为 /something/static/
) - android - 失败的 sepolicy 检查 - Android 10 构建 - Pixel 3a XL