PostgreSQL存储过程：如何根据传入参数返回不同表结构

Alright, let's figure out how to build a PostgreSQL stored procedure that returns different table structures based on input parameters. This is a common scenario when you need a single entry point for fetching data from multiple related tables, and here are a couple of reliable approaches to make it work:

Approach 1: Using REF CURSORS (Recommended for Procedures) #

PostgreSQL's ref cursors let you dynamically return result sets with varying structures. This is ideal for stored procedures since they're designed for transactional operations and can output cursors directly.

Example Procedure #

CREATE OR REPLACE PROCEDURE get_dynamic_data(p_table_identifier TEXT, OUT p_result refcursor)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Assign a fixed cursor name for consistency
    p_result := 'dynamic_data_cursor';

    -- Branch logic based on input parameter
    CASE p_table_identifier
        WHEN 'customers' THEN
            OPEN p_result FOR
                SELECT customer_id, first_name, last_name, email, signup_date FROM customers;
        WHEN 'orders' THEN
            OPEN p_result FOR
                SELECT order_id, customer_id, order_date, total_amount, status FROM orders;
        WHEN 'products' THEN
            OPEN p_result FOR
                SELECT product_id, product_name, category, price, stock_quantity FROM products;
        ELSE
            -- Throw an error for invalid parameters
            RAISE EXCEPTION 'Unsupported table identifier: %', p_table_identifier;
    END CASE;
END;
$$;

How to Call It #

In psql or a client that supports cursors, you'll need to run it within a transaction:

-- Start a transaction
BEGIN;
-- Call the procedure and fetch the cursor
CALL get_dynamic_data('customers', :result_cursor);
FETCH ALL IN "dynamic_data_cursor";
-- Commit to close the cursor
COMMIT;

Approach 2: Polymorphic Function (Type-Safe Alternative) #

If you prefer using functions (which are better for querying directly), you can create a polymorphic function that returns a SETOF record. You'll need to define the return structure when calling the function, which adds type safety.

Example Function #

CREATE OR REPLACE FUNCTION get_dynamic_data(p_table_identifier TEXT)
RETURNS SETOF record
LANGUAGE plpgsql
AS $$
BEGIN
    CASE p_table_identifier
        WHEN 'customers' THEN
            RETURN QUERY
                SELECT customer_id, first_name, last_name, email, signup_date FROM customers;
        WHEN 'orders' THEN
            RETURN QUERY
                SELECT order_id, customer_id, order_date, total_amount, status FROM orders;
        WHEN 'products' THEN
            RETURN QUERY
                SELECT product_id, product_name, category, price, stock_quantity FROM products;
        ELSE
            RAISE EXCEPTION 'Unsupported table identifier: %', p_table_identifier;
    END CASE;
END;
$$;

How to Call It #

You need to specify the return structure either by matching an existing table's schema or defining columns explicitly:

-- Use an existing table's structure for type matching
SELECT * FROM get_dynamic_data('customers') AS customers;

-- Define columns explicitly (useful if you don't want all columns)
SELECT * FROM get_dynamic_data('orders') AS (
    order_id INT,
    customer_id INT,
    order_date DATE,
    total_amount NUMERIC(10,2)
);

Key Notes #

• Error Handling: Always include exception handling for invalid parameters to avoid silent failures.
• Permissions: Ensure the role executing the procedure/function has SELECT access to all underlying tables.
• Performance: Both approaches perform similarly, but functions are easier to integrate into regular queries, while procedures are better for transactional workflows.

内容的提问来源于stack exchange，提问作者Charlie