sql - PostgreSQL 将对象列表插入存储过程

Question

我目前正在学习 PostgreSQL，但在存储过程方面遇到了瓶颈。

我有一个包含这些列的订单表:

OrderId
OrderStatus
OrderTime

我还有一个订单行表

OrderId
OrderLineId
OrderLineAmount
OrderLineCost

我正在尝试编写一个存储过程来创建订单，然后将订单行列表插入到第二个表中。

这是我目前所拥有的:

CREATE OR REPLACE FUNCTION public.createcustomerorder(
_orderstatus integer, 
_ordertimestamp timestamp)
RETURNS int4 AS
$BODY$
  DECLARE 
    last_id int4;
  BEGIN

  INSERT INTO "Order"
    (orderstatus, ordertimestamp)
    VALUES(_orderstatus, _ordertimestamp)
    RETURNING orderid INTO last_id;
RETURN last_id;

END;
$BODY$
LANGUAGE plpgsql;

我发现很难弄清楚如何传递参数，然后如何进行实际插入。

谢谢你的时间

Answer 1

有几种方法可以将记录列表传递给函数。

使用简单的表格作为示例，您可以根据您的模式调整它:

create table t(id serial primary key, x int, y text);

JSON

create function foo_j(data JSON) returns void language plpgsql as $$
begin
  insert into t(x, y)
  select (d->>'x')::int, (d->>'y')::text
  from json_array_elements(data) as d;
  return;
end $$;

用法:

select foo_j('[{"x": 1, "y": "a"}, {"x": 2, "y": "b"}]');

数组

要使用数组，您需要为其元素声明类型。它将包含您的表的可变字段，在我们的示例中，除了 id:

create type t_data as (x int, y text);

create function foo_a(data t_data[]) returns void language plpgsql as $$
begin
  insert into t(x, y)
  select d.x, d.y
  from unnest(data) as d;
  return;
end $$;

用法:

select foo_a(array[(1, 'a'), (2, 'b')]::t_data[]);

可变参数

几乎相同，但函数声明及其调用不同:

create function foo_v(variadic data t_data[]) returns void language plpgsql as $$
begin
  insert into t(x, y)
  select d.x, d.y
  from unnest(data) as d;
  return;
end $$;

用法:

select foo_v((1, 'a'), (2, 'b'));

文档:

• JSON type
• Arrays
• Variadic