Temporary Table

Introduction

This is a data storage mechanism used to solve the problem of temporary data storage.
Although it is called a Temporary Table, this is actually a real table created in the database just like other normal tables.

Characteristics

The scope of existence of a Temporary Table is throughout that Session, until you disconnect.
It is stored like a real table and can be written to disk if it exceeds temp_buffers.
It has all the properties of a physical table, such as the ability to create indexes to speed up data queries, run ANALYZE or view the table structure.
Its lifecycle only lasts throughout that Session, when you disconnect from the database, this temporary table will be automatically deleted, or you can also actively execute DROP TABLE.
Because it exists throughout the session, it can be called for use multiple times in different statements and also within Functions or Stored Procedures.

Use cases

The following are appropriate cases to use a Temporary Table, including

Very large intermediate datasets with millions of rows where you need to create an Index on them so that subsequent statements query faster.
You need to share this temporary dataset across multiple different SQL statements within the same Function/Procedure or long script.
You need to perform multiple complex operations such as INSERT, UPDATE and DELETE directly on that temporary dataset.

Detail

Please create tables as follows

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(150),
    sku VARCHAR(50)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20), 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT,
    price_per_unit DECIMAL(10, 2)
);

PROCEDURE

CREATE OR REPLACE PROCEDURE sp_run_daily_product_performance()
LANGUAGE plpgsql
AS $$
BEGIN
    CREATE TEMP TABLE IF NOT EXISTS tmp_product_stats (
        product_id INT,
        total_qty_sold INT DEFAULT 0,
        total_revenue DECIMAL(12, 2) DEFAULT 0.00,
        cancelled_qty INT DEFAULT 0,
        performance_score DECIMAL(5, 2) DEFAULT 0.00
    ) ON COMMIT PRESERVE ROWS;

    TRUNCATE TABLE tmp_product_stats;

    INSERT INTO tmp_product_stats (product_id, total_qty_sold, total_revenue)
    SELECT 
        oi.product_id,
        SUM(oi.quantity) AS total_qty_sold,
        SUM(oi.quantity * oi.price_per_unit) AS total_revenue
    FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.id
    WHERE o.status = 'COMPLETED' 
      AND o.created_at >= CURRENT_DATE
    GROUP BY oi.product_id;

    CREATE INDEX IF NOT EXISTS idx_tmp_product_id ON tmp_product_stats(product_id);

    UPDATE tmp_product_stats t
    SET cancelled_qty = sub.total_cancelled
    FROM (
        SELECT oi.product_id, SUM(oi.quantity) AS total_cancelled
        FROM order_items oi
        INNER JOIN orders o ON oi.order_id = o.id
        WHERE o.status = 'CANCELLED' 
          AND o.created_at >= CURRENT_DATE
        GROUP BY oi.product_id
    ) sub
    WHERE t.product_id = sub.product_id;

    UPDATE tmp_product_stats
    SET performance_score = (total_qty_sold * 10) - (cancelled_qty * 5);

    DELETE FROM tmp_product_stats
    WHERE performance_score <= 0;

    RAISE NOTICE 'Procedure completed: The temporary table `tmp_product_stats` is ready for querying.';
END;
$$;

CALL sp_run_daily_product_performance();

SELECT 
    t.product_id,
    p.name AS product_name,
    p.sku,
    t.total_qty_sold,
    t.total_revenue,
    t.cancelled_qty,
    t.performance_score
FROM tmp_product_stats t
INNER JOIN products p ON t.product_id = p.id
ORDER BY t.performance_score DESC;

DROP TABLE IF EXISTS tmp_product_stats;

The example above is used to get performance statistics of products sold during the day.
This is how to use a TEMP TABLE within a PROCEDURE, it includes features like a normal table such as
- Create if not exist
- TRUNCATE to clear old data
- INSERT the COMPLETED orders of the day
- Ability to create an INDEX for better querying
- Use UPDATE to calculate the total cancelled quantity and compute the performance score for each product according to the formula (total_qty_sold * 10) - (cancelled_qty * 5)
- Use DELETE to remove products with low performance scores (performance_score <= 0)
When calling the PROCEDURE, the TEMP TABLE will be created and becomes available for use.
Note that each time you want statistics, you must call the PROCEDURE again to get the latest data, because the TEMP TABLE does not automatically update data.
If it is no longer used, you can DROP the table as usual, or when the session closes, the table will be automatically deleted.

FUNCTION

This is a similar example to the one above but replaced with a FUNCTION so that when used, you will have the complete data to view the results directly.

CREATE OR REPLACE FUNCTION fn_get_daily_product_performance()
RETURNS TABLE (
    product_id INT,
    product_name VARCHAR(150),
    sku VARCHAR(50),
    total_qty_sold INT,
    total_revenue DECIMAL(12, 2),
    cancelled_qty INT,
    performance_score DECIMAL(5, 2)
) 
LANGUAGE plpgsql
AS $$
BEGIN
    CREATE TEMP TABLE IF NOT EXISTS tmp_report_data (
        p_id INT,
        qty_sold INT DEFAULT 0,
        revenue DECIMAL(12, 2) DEFAULT 0.00,
        qty_cancelled INT DEFAULT 0,
        score DECIMAL(5, 2) DEFAULT 0.00
    ) ON COMMIT DROP; 

    TRUNCATE TABLE tmp_report_data;

    INSERT INTO tmp_report_data (p_id, qty_sold, revenue)
    SELECT 
        oi.product_id,
        SUM(oi.quantity)::INT,
        SUM(oi.quantity * oi.price_per_unit)
    FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.id
    WHERE o.status = 'COMPLETED' 
      AND o.created_at >= CURRENT_DATE
    GROUP BY oi.product_id;

    CREATE INDEX IF NOT EXISTS idx_tmp_report_p_id ON tmp_report_data(p_id);

    UPDATE tmp_report_data t
    SET qty_cancelled = sub.total_cancelled
    FROM (
        SELECT oi.product_id, SUM(oi.quantity)::INT AS total_cancelled
        FROM order_items oi
        INNER JOIN orders o ON oi.order_id = o.id
        WHERE o.status = 'CANCELLED' 
          AND o.created_at >= CURRENT_DATE
        GROUP BY oi.product_id
    ) sub
    WHERE t.p_id = sub.product_id;

    UPDATE tmp_report_data
    SET score = (qty_sold * 10) - (qty_cancelled * 5);

    DELETE FROM tmp_report_data
    WHERE score <= 0;

    RETURN QUERY
    SELECT 
        t.p_id,
        p.name,
        p.sku,
        t.qty_sold,
        t.revenue,
        t.qty_cancelled,
        t.score
    FROM tmp_report_data t
    INNER JOIN products p ON t.p_id = p.id
    ORDER BY t.score DESC;
END;
$$;

SELECT * FROM fn_get_daily_product_performance();

SELECT product_name, total_revenue, performance_score 
FROM fn_get_daily_product_performance()
LIMIT 10;

I still perform the same actions as in the PROCEDURE including CREATE (when creating a TEMP TABLE combined with ON COMMIT DROP, it will automatically delete the table upon COMMIT, which is when the FUNCTION ends), TRUNCATE, create INDEX, UPDATE and DELETE.
At the end of the FUNCTION, you just need to use RETURN QUERY to view the results directly.
As a result, you can just use fn_get_daily_product_performance() like a normal table and query arbitrarily according to your needs.
Note that after using this FUNCTION, the TEMP TABLE no longer exists, so it cannot be used further, you can adjust it yourself to not DROP the table according to your needs.