Suppose I have a complex PostgreSQL function designed to aggregate and report data from several related tables in a database. For example it can look like this:
CREATE OR REPLACE FUNCTION public.aggregate_sales_data(
sales_rep_id integer)
RETURNS TABLE(product_id integer, customer_id integer, total_sales_amount numeric, last_sale_date timestamp with time zone, region_id smallint)
LANGUAGE 'plpgsql'
COST 100
VOLATILE SECURITY DEFINER PARALLEL UNSAFE
ROWS 1000
AS $BODY$
DECLARE
BEGIN
RETURN QUERY
SELECT DISTINCT ON (s.product_id) s.product_id, c.customer_id, SUM(s.amount) as total_sales_amount, MAX(s.sale_date) as last_sale_date, MAX(s.region_id) as region_id
FROM sales s
LEFT JOIN customers c ON s.customer_id = c.customer_id
WHERE s.sale_date > CURRENT_TIMESTAMP - INTERVAL '1 month' AND s.sales_rep_id = sales_rep_id
GROUP BY s.product_id, c.customer_id
ORDER BY s.product_id, c.customer_id ASC
LIMIT 500;
END;
$BODY$;
Now I want this function to return additional row.
Lets say
(2, 2, 3, NOW(), 1).
Can I perform insert using this function without making manual inserts into tables customers and sales?
In this specific case it might not be hard but sometimes the function can have multiple joints and its a pretty tedious work to do that. Usually I need to do that for testing, maybe there's an another approach I am missing?
Possible solution is to use a hardcoded union inside of the function, but I prefer a more 'correct' approach if there is one.
UPDATE
Let's say we do this:
insert_customers_and_products(123, 456, 100.00, '2024-03-06 10:00:00', 1);
We get this:
SELECT * FROM customers WHERE customer_id = 456;
Returns (456, null)
SELECT * FROM products WHERE product_id = 123;
Returns (123, 100, '2024-03-06 10:00:00', 1, null, null, null)
SELECT * FROM aggregate_sales_data()
WHERE product_id = 123 AND customer_id = 456;
Returns (123, 456, 100.00, '2024-03-06 10:00:00', 1)
What should be in insert_customers_and_products? Can we make it without handwriting inserts for each table? Union would give us the last select, but is it possible to achieve all three of them using the initial function somehow?
Use with clause in postgresql which helps to create a temporary table.it drop the table at the end of the session. Refer this link https://www.postgresql.org/docs/current/queries-with.html