How to Calculate SaaS Pricing in PostgreSQL

Galaxy Glossary

How do I calculate tiered SaaS pricing directly in PostgreSQL?

Use a reusable SQL function to return per-customer SaaS subscription prices directly inside PostgreSQL.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why calculate SaaS pricing inside PostgreSQL?

Keeping pricing logic in the database lets engineering, finance, and analytics teams share one trusted source of truth. It also removes duplicated business logic from application code and scheduled jobs.

What does the price_saas() function return?

The function outputs the total subscription price based on plan name, seat count, and currency. You can embed it in SELECT, INSERT, or UPDATE statements, or expose it through a view or API.

How do I create the price_saas() function?

Use CREATE OR REPLACE FUNCTION with a CASE expression for tiered pricing. Store rates in a lookup table for maximum flexibility, or hard-code values for speed. Mark the function IMMUTABLE so PostgreSQL can cache results.

Step-by-step creation

1️⃣ Define input parameters: plan_name TEXT, seat_count INT, currency TEXT DEFAULT 'USD'.
2️⃣ Return NUMERIC(10,2) to avoid rounding errors.
3️⃣ Handle unsupported plans with an EXCEPTION.

How do I apply the function to real data?

Join Customers to Orders, aggregate seat counts, and call price_saas() to compute monthly recurring revenue (MRR) or upcoming invoices—all in one query.

Best practices for SaaS pricing queries

Cache lookups in a CTE, index plan_name, and validate seat_count > 0 before calling the function. Version your pricing table so historical invoices always match past rates.

Common mistakes and fixes

Hard-coding rates in every query: centralize logic in a function or table.
Forgetting to mark IMMUTABLE: PostgreSQL skips optimizations if you leave the setting out.

When should I update pricing logic?

Introduce a new row in the pricing table or add a CASE branch. Re-run tests and backfill only future invoices to preserve auditability.

Why How to Calculate SaaS Pricing in PostgreSQL is important

How to Calculate SaaS Pricing in PostgreSQL Example Usage


-- Calculate next month's invoice for every active customer
WITH seat_counts AS (
    SELECT o.customer_id, SUM(oi.quantity) AS seats
    FROM   Orders o
    JOIN   OrderItems oi ON oi.order_id = o.id
    WHERE  date_trunc('month', o.order_date) = date_trunc('month', CURRENT_DATE)
    GROUP  BY o.customer_id
)
SELECT c.id,
       c.name,
       price_saas('Pro', sc.seats) AS invoice_amount
FROM   Customers c
JOIN   seat_counts sc ON sc.customer_id = c.id
ORDER  BY invoice_amount DESC;

How to Calculate SaaS Pricing in PostgreSQL Syntax


CREATE OR REPLACE FUNCTION price_saas(
    plan_name   TEXT,
    seat_count  INT,
    currency    TEXT DEFAULT 'USD'
) RETURNS NUMERIC(10,2) AS $$
DECLARE
    base_rate NUMERIC(10,2);
BEGIN
    -- Simple tier mapping; replace with SELECT from pricing table if preferred
    CASE plan_name
        WHEN 'Starter' THEN base_rate := 10;    -- USD per seat
        WHEN 'Pro'     THEN base_rate := 20;
        WHEN 'Enterprise' THEN base_rate := 35;
        ELSE RAISE EXCEPTION 'Unknown plan %', plan_name;
    END CASE;
    RETURN base_rate * seat_count;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Example use inside a SELECT
SELECT c.id, c.name,
       price_saas('Pro', oi.quantity) AS monthly_price
FROM   Customers AS c
JOIN   Orders    AS o  ON o.customer_id = c.id
JOIN   OrderItems AS oi ON oi.order_id   = o.id;

Common Mistakes

Hard-coding plan prices in multiple queries—use a single price_saas() function or a pricing lookup table instead.
Leaving the function VOLATILE—mark it IMMUTABLE so PostgreSQL can cache results and speed up large invoice runs.

Frequently Asked Questions (FAQs)

Can I store prices in a table instead of CASE?

Yes. Replace the CASE block with a SELECT FROM pricing WHERE plan_name = $1. This keeps business users out of source control and allows effective-date pricing.