PostgreSQL inserting list of objects into a stored procedure

Question

I am currently learning PostgreSQL and have hit a wall when it comes to stored procedures.

I have an order table with these columns:

OrderId
OrderStatus
OrderTime

I also have an order line table

OrderId
OrderLineId
OrderLineAmount
OrderLineCost

I am trying to write a stored procedure which would create an order and then insert a list of order lines into the second table.

Here is what I have so far:

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;

I am finding it difficult for figure out how I can pass in the parameters, and then how to do the actual insert.

Thanks for your time

Abelisto · Accepted Answer · 2018-08-01 20:34:48Z

9

There are several ways to pass a list of records to a function.

Using the simple table for examples, you can to adapt it to your schema:

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 $$;

Usage:

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

Array

To use the array you need to declare the type for its elements. It will contains variable fields for your table, in our example all except 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 $$;

Usage:

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

Variadic

Almost same but difference in the function declaration and its call:

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 $$;

Usage:

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

Documentation:

answered Aug 1, 2018 at 20:34

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

questionto42 Over a year ago

Might get overseen: better also write the create type t_data as (x int, y text); for the variadic block.

Collectives™ on Stack Overflow

PostgreSQL inserting list of objects into a stored procedure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related