How to run many inserts related data into PostgreSQL functions?

Question

Im asking for help in this case: I want to create function for insert data into 3 tables.

CREATE TABLE "public"."loads" (
  "id" int4 NOT NULL DEFAULT nextval('loads_id_seq'::regclass),
  "user_id" int4 NOT NULL,
  "name" varchar(60) COLLATE "pg_catalog"."default",
  "weight" float4 NOT NULL,
  "capacity" float4,
  "packing_type" int4,
  "price_request" bool
)

CREATE TABLE "public"."load_loading" (
  "load_id" int4 NOT NULL,
  "date_from" date,
  "date_to" date,
  "addr" varchar(120) COLLATE "pg_catalog"."default",
  "time_min" varchar(6) COLLATE "pg_catalog"."default",
  "time_max" varchar(6) COLLATE "pg_catalog"."default"
)

CREATE TABLE "public"."load_unloading" (
  "load_id" int4 NOT NULL,
  "date_from" date NOT NULL,
  "date_to" date,
  "addr" varchar(120) COLLATE "pg_catalog"."default",
  "time_min" varchar(6) COLLATE "pg_catalog"."default",
  "time_max" varchar(6) COLLATE "pg_catalog"."default"
)

I wrote a function, but cant get any results.

CREATE OR REPLACE FUNCTION "public"."add_load"("load_date_from" date, "loading_addr" varchar, "unloading_date_from" date, "unloading_addr" varchar, "_user_id" int4, "_name" varchar, "_weight" float4, "_capacity" float4, "_packing_type" int4, "_price_request" bool)
  RETURNS TABLE("_load" "public"."loads", "_load_load" "public"."load_loading", "_load_unload" "public"."load_unloading") AS $BODY$
      BEGIN
                RETURN QUERY
                WITH _load AS (
        INSERT INTO loads(user_id, name, weight, capacity, packing_type, price_request)
        VALUES(_user_id, _name, _weight, _capacity, _packing_type, _price_request) RETURNING *)
                , _load_load AS (INSERT INTO load_loading (load_id, date_from, addr)
                VALUES ((select id from _load), load_date_from, loading_addr) RETURNING *)
                ,   _load_unload AS (INSERT INTO load_unloading(load_id, date_from, addr) 
                VALUES ((SELECT id FROM _load), unloading_date_from, unloading_addr) RETURNING *)
                TABLE _load;
      END;
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000

When i call she i have this error:

select * from add_load('10-10-2019', 'addr1', '10-11-2019', 'addr2', 123, 'name', 55, 55, 1, true)
> ERROR:  structure of query does not match function result type
DETAIL:  Returned type integer does not match expected type loads in column 1.
CONTEXT:  PL/pgSQL function add_load(date,character varying,date,character varying,integer,character varying,real,real,integer,boolean) line 3 at RETURN QUERY

I expect from my function see result like all columns from 3 tables;

And another question, is there a way to get the result like this:

id, user_id, name, weight, capacity, packing_type, price_request, {load_loading data}, {load_unloading_data}

Answer 1

The error above is because the function returns :

RETURNS TABLE(_load loads, _load_load load_loading, _load_unload load_unloading) AS $BODY$

and your query returns:

| id | user_id | name | weight | capacity | packing_type | price_request |

If you remove this part:

, _load_load load_loading, _load_unload load_unloading

The function will work as in my DEMO

And for your second question: You should name every column by it's own to do that. Here is the DEMO for that result. And here is the function:

CREATE OR REPLACE FUNCTION add_load(load_date_from date
                                    , loading_addr varchar(140)
                                    , unloading_date_from date
                                    , unloading_addr varchar(140)
                                    , _user_id int4
                                    , _name varchar(140)
                                    , _weight float4
                                    , _capacity float4
                                    , _packing_type int4
                                    , _price_request bool)
  RETURNS TABLE( id int4, user_id1 int4, name varchar(60), weight float4, capacity float4,
                 packing_type int4, price_request bool, load_id1 int4, date_from1 date,
                 date_to1 date, addr1 varchar(120), time_min1 varchar(6),
                 time_max1 varchar(6), load_id2 int4, date_from2 date,  date_to2 date,
                 addr2 varchar(120), time_min2 varchar(6),  time_max2 varchar(6)
                 ) AS $BODY$
      BEGIN

                RETURN QUERY 
                WITH _load AS (
        INSERT INTO loads(user_id, name, weight, capacity, packing_type, price_request)
        VALUES(_user_id::int, _name::varchar, _weight::float, _capacity::float, _packing_type::int, _price_request::bool) RETURNING *)
                , _load_load AS (INSERT INTO load_loading (load_id, date_from, addr)
                VALUES ((select user_id::int from loads), load_date_from::date, loading_addr::varchar) RETURNING *)
                ,   _load_unload AS (INSERT INTO load_unloading(load_id, date_from, addr) 
                VALUES ((SELECT user_id::int FROM loads), unloading_date_from::date, unloading_addr::varchar) RETURNING *)
                select * from _load, _load_load, _load_unload;
      END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000