Fonction Postgres pour insérer plusieurs enregistrements dans deux tables

Question

create table public.orders (
    orderID serial PRIMARY KEY,
    orderdate timestamp NOT NULL
);

create table public.orderdetails (
    orderdetailID serial PRIMARY KEY,
    orderID integer REFERENCES public.orders(orderID),
    item varchar(20) NOT NULL,
    quantity INTEGER NOT NULL
);

J'ai des tableaux (exemple très simplifié) comme ci-dessus, dans lesquels je veux insérer les détails d'une commande et les détails de la commande en une seule action.

Je connais les transactions et je peux insérer des données à l'aide d'une commande SQL telle que celle décrite ci-dessous :

DO $$
  DECLARE inserted_id integer;
  BEGIN
    INSERT INTO public.orders(orderdate) VALUES (NOW()) RETURNING orderID INTO inserted_id;

    INSERT INTO public.orderdetails(orderID, item, quantity)
    VALUES (inserted_id, 'Red Widget', 10),
           (inserted_id, 'Blue Widget', 5);
  END
$$ LANGUAGE plpgsql;

Cependant, dans l'idéal, j'aimerais qu'une requête comme celle qui précède soit une fonction, si possible, plutôt que d'être stockée dans mon application.

Je ne sais pas si j'ai besoin d'une aide financière, mais j'ai besoin d'une aide financière. Si ce que je cherche à faire est considéré comme une mauvaise pratique, merci de m'indiquer quelle autre voie je devrais suivre.

Merci d'avance.

Answer 1

Vous pouvez utiliser un tableau de tuples pour transmettre plusieurs lignes à la fonction. Vous avez besoin d'un type personnalisé :

create type order_input as (
    item text,
    quantity integer);

Utiliser un tableau de ce type comme argument de la fonction :

create or replace function insert_into_orders(order_input[])
returns void language plpgsql as $$
declare 
    inserted_id integer;
begin
    insert into public.orders(orderdate) 
    values (now()) 
    returning orderid into inserted_id;

    insert into public.orderdetails(orderid, item, quantity)
    select inserted_id, item, quantity
    from unnest($1);
end $$;

Utilisation :

select insert_into_orders(
    array[
        ('Red Widget', 10), 
        ('Blue Widget', 5)
    ]::order_input[]
);

select * from orderdetails;

 orderdetailid | orderid |    item     | quantity 
---------------+---------+-------------+----------
             1 |       1 | Red Widget  |       10
             2 |       1 | Blue Widget |        5
(2 rows)

Answer 2

Merci Klin. Cela m'a beaucoup aidé.

De plus, j'ai pu éviter l'utilisation d'un type explicite et me contenter d'utiliser le tableau défini comme un tableau.

Code ci-dessous :

-- Create table whose type will be passed as input parameter
create table tbl_card
(id integer,
name varchar(10),
cardno bigint)

-- Create function to accept an array of table
create or replace function fn_insert_card_arr (tbl_card[]) returns integer as $$
begin
insert into tbl_card (id, name,cardno)
select id, name, cardno
from unnest($1);

return 0;
end;
$$ LANGUAGE plpgsql;

-- Execute function by passing an array of table (type casted to array of type table)
select fn_insert_card_arr(
array[
    (1,'one', 2222777744448888), 
    (2,'two', 8888444466662222),
    (3,'three', 2222777744448888), 
    (4,'four', 8888444466662222)
]::tbl_card[]
);