Creating Reports

Overview

Database functions provide an effective way to create parameterized, reusable reports in Metadb. Instead of writing the same query with different values, you can define a function once and call it with different parameters.

Metadb does not support database views. Use database functions instead, as they provide better flexibility and don’t create blocking dependencies during schema updates.

Why Use Database Functions?

Database functions offer several advantages for reporting:

Parameterization

Define flexible queries that accept different input values

Reusability

Write once, call many times with different parameters

Sharing

Grant execution privileges to other users

Consistency

Ensure all users run the same logic with standardized business rules

Creating a Basic Report Function

Let’s walk through creating a parameterized report function.

Start with a base query

Begin with a working SQL query:

SELECT item_id,
       COUNT(*) AS loan_count
FROM folio_circulation.loan__t
WHERE '2023-01-01' <= loan_date AND loan_date < '2024-01-01'
GROUP BY item_id;

This counts loans for each item in 2023.

Identify parameters

Determine which values should be parameterized. In this example:

Start date: '2023-01-01'
End date: '2024-01-01'

Create the function

Convert the query into a function with parameters:

CREATE FUNCTION lisa.count_loans(
    start_date DATE DEFAULT '2000-01-01',
    end_date DATE DEFAULT '2050-01-01'
)
RETURNS TABLE(
    item_id UUID,
    loan_count INTEGER
)
AS $$
    SELECT item_id,
           COUNT(*) AS loan_count
    FROM folio_circulation.loan__t
    WHERE start_date <= loan_date AND loan_date < end_date
    GROUP BY item_id
$$
LANGUAGE SQL
STABLE
PARALLEL SAFE;

Call the function

Execute the function with different parameters:

-- Loans from 2022
SELECT * FROM lisa.count_loans(
    start_date => '2022-01-01', 
    end_date => '2023-01-01'
);

-- Loans from 2023 onward (uses default end_date)
SELECT * FROM lisa.count_loans(
    start_date => '2023-01-01'
);

Using named parameters (start_date =>) makes function calls more readable and allows you to omit parameters that have defaults.

Function Components Explained

Function Signature

CREATE FUNCTION lisa.count_loans(
    start_date DATE DEFAULT '2000-01-01',
    end_date DATE DEFAULT '2050-01-01'
)

Function name: lisa.count_loans (created in user lisa’s workspace)
Parameters: Each parameter has a name, data type, and optional default value
Default values: Allow calling the function with fewer arguments

Return Type

RETURNS TABLE(
    item_id UUID,
    loan_count INTEGER
)

Defines the structure of the result set. Each column must have a name and data type.

Function Body

AS $$
    SELECT item_id,
           COUNT(*) AS loan_count
    FROM folio_circulation.loan__t
    WHERE start_date <= loan_date AND loan_date < end_date
    GROUP BY item_id
$$

The SQL query enclosed in $$ delimiters. Parameter names can be referenced directly in the query.

Function Properties

LANGUAGE SQL
STABLE
PARALLEL SAFE;

LANGUAGE SQL: Specifies this is a SQL function
STABLE: Indicates the function returns the same result for the same inputs within a transaction (appropriate for queries on changing data)
PARALLEL SAFE: Allows the function to be executed in parallel for better performance

Once you’ve created a report function, share it with colleagues:

Grant schema access

GRANT USAGE ON SCHEMA lisa TO celia, rosalind;

Grant execution privileges

GRANT EXECUTE ON FUNCTION lisa.count_loans TO celia, rosalind;

Users can now call the function

Other users can execute:

SELECT * FROM lisa.count_loans(
    start_date => '2023-01-01',
    end_date => '2024-01-01'
);

If you’ve already granted USAGE ON SCHEMA to a user previously, you don’t need to run it again when sharing additional functions.

Advanced Examples

Example 1: Multi-Parameter Report

CREATE FUNCTION analytics.patron_activity(
    origin_filter TEXT DEFAULT '%',
    group_filter TEXT DEFAULT '%',
    min_loan_count INTEGER DEFAULT 0
)
RETURNS TABLE(
    patron_id UUID,
    patron_group TEXT,
    origin TEXT,
    total_loans BIGINT
)
AS $$
    SELECT 
        p.id AS patron_id,
        pg.groupname AS patron_group,
        p.__origin AS origin,
        COUNT(l.id) AS total_loans
    FROM library.patron p
    JOIN library.patrongroup pg ON p.patron_group_id = pg.id
    LEFT JOIN folio_circulation.loan__t l ON l.patron_id = p.id
    WHERE p.__origin LIKE origin_filter
      AND pg.groupname LIKE group_filter
    GROUP BY p.id, pg.groupname, p.__origin
    HAVING COUNT(l.id) >= min_loan_count
    ORDER BY total_loans DESC
$$
LANGUAGE SQL
STABLE
PARALLEL SAFE;

Usage:

-- All patrons with at least 10 loans
SELECT * FROM analytics.patron_activity(min_loan_count => 10);

-- Graduate students from east campus
SELECT * FROM analytics.patron_activity(
    origin_filter => 'east',
    group_filter => 'graduate'
);

Example 2: Summary Report with Aggregations

CREATE FUNCTION reports.circulation_summary(
    report_year INTEGER
)
RETURNS TABLE(
    month DATE,
    total_loans BIGINT,
    unique_items BIGINT,
    unique_patrons BIGINT
)
AS $$
    SELECT 
        DATE_TRUNC('month', loan_date)::DATE AS month,
        COUNT(*) AS total_loans,
        COUNT(DISTINCT item_id) AS unique_items,
        COUNT(DISTINCT patron_id) AS unique_patrons
    FROM folio_circulation.loan__t
    WHERE EXTRACT(YEAR FROM loan_date) = report_year
    GROUP BY DATE_TRUNC('month', loan_date)
    ORDER BY month
$$
LANGUAGE SQL
STABLE
PARALLEL SAFE;

Usage:

SELECT * FROM reports.circulation_summary(2023);

Example 3: Historical Comparison Function

CREATE FUNCTION analytics.compare_periods(
    metric_date DATE,
    comparison_date DATE
)
RETURNS TABLE(
    group_name TEXT,
    current_count BIGINT,
    comparison_count BIGINT,
    change_count BIGINT,
    percent_change NUMERIC(10,2)
)
AS $$
    WITH current_data AS (
        SELECT groupname, COUNT(*) AS cnt
        FROM library.patrongroup__
        WHERE __start <= metric_date AND metric_date < __end
        GROUP BY groupname
    ),
    comparison_data AS (
        SELECT groupname, COUNT(*) AS cnt
        FROM library.patrongroup__
        WHERE __start <= comparison_date AND comparison_date < __end
        GROUP BY groupname
    )
    SELECT 
        COALESCE(c.groupname, p.groupname) AS group_name,
        COALESCE(c.cnt, 0) AS current_count,
        COALESCE(p.cnt, 0) AS comparison_count,
        COALESCE(c.cnt, 0) - COALESCE(p.cnt, 0) AS change_count,
        CASE 
            WHEN COALESCE(p.cnt, 0) = 0 THEN NULL
            ELSE ROUND(((COALESCE(c.cnt, 0)::NUMERIC - p.cnt) / p.cnt * 100), 2)
        END AS percent_change
    FROM current_data c
    FULL OUTER JOIN comparison_data p ON c.groupname = p.groupname
    ORDER BY group_name
$$
LANGUAGE SQL
STABLE
PARALLEL SAFE;

Best Practices

Use Descriptive Names

-- Good: Clear purpose
CREATE FUNCTION reports.monthly_loan_summary(...)

-- Avoid: Vague naming
CREATE FUNCTION reports.report1(...)

Provide Default Values

Defaults make functions easier to call:

CREATE FUNCTION reports.recent_activity(
    days_back INTEGER DEFAULT 30  -- Sensible default
)

Document Your Functions

Use PostgreSQL comments:

COMMENT ON FUNCTION lisa.count_loans IS 
    'Counts loans per item within a date range. Parameters: start_date, end_date. Returns: item_id, loan_count.';

Choose Appropriate Volatility

Volatility	Use When	Example
`IMMUTABLE`	Result never changes for same inputs	Mathematical functions
`STABLE`	Result consistent within a transaction	Most analytical queries
`VOLATILE`	Result can change at any time	Functions using `CURRENT_TIMESTAMP`

For analytical queries on Metadb data, use STABLE. This is appropriate for data that changes between transactions but remains consistent within a single transaction.

Integration with BI Tools

Database functions work seamlessly with web-based database tools like CloudBeaver:

Create functions in your workspace or a shared schema
Grant execution privileges to report users
Users access functions through the BI tool interface
Functions appear as callable stored procedures
Parameters can be entered through the tool’s UI

Combine database functions with user workspaces to create a complete reporting infrastructure.

Why Not Use Views?

Metadb does not support database views because:

Schema dependencies: Views can block required schema changes during data updates
Hidden complexity: Views hide query complexity behind what appears to be a simple table
Better alternative: Functions provide parameterization and don’t create blocking dependencies

Always use database functions instead of views in Metadb environments.

Get Started

Core Concepts

User Guide

Administration

Creating Reports

Overview

Why Use Database Functions?

Parameterization

Reusability

Sharing

Consistency

Creating a Basic Report Function

Function Components Explained

Function Signature

Return Type

Function Body

Function Properties

Advanced Examples

Example 1: Multi-Parameter Report

Example 2: Summary Report with Aggregations

Example 3: Historical Comparison Function

Best Practices

Use Descriptive Names

Provide Default Values

Document Your Functions

Choose Appropriate Volatility

Integration with BI Tools

Why Not Use Views?

Build docs developers (and LLMs) love

Get Started

Core Concepts

User Guide

Administration

​Overview

​Why Use Database Functions?

Parameterization

Reusability

Sharing

Consistency

​Creating a Basic Report Function

​Function Components Explained

​Function Signature

​Return Type

​Function Body

​Function Properties

​Sharing Reports with Other Users

​Advanced Examples

​Example 1: Multi-Parameter Report

​Example 2: Summary Report with Aggregations

​Example 3: Historical Comparison Function

​Best Practices

​Use Descriptive Names

​Provide Default Values

​Document Your Functions

​Choose Appropriate Volatility

​Integration with BI Tools

​Why Not Use Views?

Build docs developers (and LLMs) love

Overview

Why Use Database Functions?

Creating a Basic Report Function

Function Components Explained

Function Signature

Return Type

Function Body

Function Properties

Sharing Reports with Other Users

Advanced Examples

Example 1: Multi-Parameter Report

Example 2: Summary Report with Aggregations

Example 3: Historical Comparison Function

Best Practices

Use Descriptive Names

Provide Default Values

Document Your Functions

Choose Appropriate Volatility

Integration with BI Tools

Why Not Use Views?