Good day
I’ve
got a performance and best practice question. We’re busy writing our
persistence framework for our application port to PostgreSQL.
We have business entities that are split over multiple tables and in an
effort to not have multiple trips to the database we’re trying to batch
these together. Some of the extensions uses serials, necessitating output
from the one query to be used in the other. It is at this point that we’re
running into the PostgreSQL limitation of only declaring variables in
functions.
I've come up with generating functions on the go, but I'm concerned about the performance impact of this. I first wanted to use an anonoumys code block, but then I cannot do parameter binding from npgsql.
Example:
create table table1
(
id bigserial,
value1 text
);
create
table table2
(
id bigserial,
value2 text
);
create
table table3
(
id bigserial,
value3 text,
table1_id1 bigint,
table1_id2 bigint,
table2_id bigint
);
I then generate this on the fly to insert a new entity
CREATE
OR REPLACE FUNCTION
tmp_641f51c9_d188_4386_93f3_c40001b191e7(table1_value1_0 Text,
table1_value1_1 Text, table2_value2_0 Text, table3_value3_0 Text)
RETURNS BIGINT AS $$
DECLARE
_table1_id1 bigint;
_table1_id2 bigint;
_table2_id bigint;
_id bigint;
table1_value1_0 ALIAS FOR $1;
table2_value2_0 ALIAS FOR $2;
table3_value3_0 ALIAS FOR $3;
BEGIN
INSERT INTO public.table1 (value1) VALUES (table1_value1_0)
RETURNING id INTO _table1_id1;
INSERT INTO public.table1 (value1) VALUES (table1_value1_1)
RETURNING id INTO _table1_id2;
INSERT INTO public.table2 (value2) VALUES (table2_value2_0)
RETURNING id INTO _table2_id;
INSERT INTO public.table3 (value3, table1_id1, table1_id2, table2_id)
VALUES (table3_value3_0, _table1_id1, _table1_id2, _table2_id)
RETURNING id INTO _id;
RETURN _id;
END;
$$ LANGUAGE plpgsql;
SELECT tmp_641f51c9_d188_4386_93f3_c40001b191e7(@table1_value1_0, @table1_value1_1, @table2_value2_0, @table3_value3_0);
DROP FUNCTION IF EXISTS tmp_641f51c9_d188_4386_93f3_c40001b191e7(Text,Text,Text,Text);
Is there a better way I'm missing and is "temp" function creation in Postgres a big performance concern, especially if a server is under load?
Regards
Riaan Stander
