如何在Amazon Redshift存储过程中实现动态表统计行数并写入审计表

Hey there, I’ve dealt with this exact dynamic table counting challenge in Redshift before—let’s get your stored procedure up and running correctly. The core issue here is that Redshift treats your VARTABLE_NAME parameter as a literal string when you use it directly, not as a table identifier. To fix this, we need to use dynamic SQL with safe identifier handling.

Working Stored Procedure Code #

Here’s a complete, tested version of your procedure that handles dynamic table names securely and writes the count to your AUDIT.RUN_TEST table:

CREATE OR REPLACE PROCEDURE AUDIT.RUN_TABLE_COUNT(
    VARSTART TIMESTAMP,
    VARRUNID VARCHAR(50),
    VARPHASE VARCHAR(50),
    VARTABLE_NAME VARCHAR(256)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_row_count BIGINT;
    v_dynamic_sql VARCHAR(1000);
BEGIN
    -- Safely generate dynamic SQL using FORMAT to handle table identifiers
    v_dynamic_sql := FORMAT('SELECT COUNT(*) FROM %I', VARTABLE_NAME);
    
    -- Execute the dynamic query and store the result in a variable
    EXECUTE v_dynamic_sql INTO v_row_count;
    
    -- Insert the audit record with the row count
    INSERT INTO AUDIT.RUN_TEST (
        START_TIME,
        RUN_ID,
        PHASE,
        TABLE_NAME,
        ROW_COUNT
    ) VALUES (
        VARSTART,
        VARRUNID,
        VARPHASE,
        VARTABLE_NAME,
        v_row_count
    );

EXCEPTION
    WHEN OTHERS THEN
        -- Optional: Log errors to a dedicated audit log table if needed
        RAISE NOTICE 'Failed to count rows for table %: %', VARTABLE_NAME, SQLERRM;
        RAISE; -- Re-throw the exception to propagate the error (adjust as needed)
END;
$$;

Key Explanations #

Let’s break down the critical parts that make this work:

1. Safe Dynamic SQL with FORMAT()

   • The FORMAT() function uses %I as a placeholder for SQL identifiers (like table names). This automatically escapes special characters, spaces, or reserved words in your table name, preventing SQL injection and ensuring valid syntax even for tricky table names (e.g., "my-table-with-spaces").
   • Without FORMAT(), concatenating strings directly (like 'SELECT COUNT(*) FROM ' || VARTABLE_NAME) would be risky and fail for tables with special characters.

2. Executing Dynamic SQL

   • EXECUTE v_dynamic_sql INTO v_row_count runs the generated query and stores the COUNT(*) result in the v_row_count variable. This is how we capture the dynamic count value to use in our insert statement.

3. Error Handling

   • The EXCEPTION block catches any errors (like a non-existent table) and raises a descriptive notice. You can extend this to write error details to a separate audit log table if you need persistent error tracking.

Important Notes #

• Permissions: Make sure the role executing this procedure has SELECT access to the table specified in VARTABLE_NAME, and INSERT access to AUDIT.RUN_TEST.
• Schema-Qualified Tables: If VARTABLE_NAME includes a schema (e.g., sales.customers), the %I placeholder will handle it correctly—no extra code needed. If you ever split schema and table into separate parameters, use two %I placeholders: FORMAT('SELECT COUNT(*) FROM %I.%I', VAR_SCHEMA, VAR_TABLE).
• Performance: For very large tables, COUNT(*) can be slow. If you don’t need an exact count, consider using Redshift’s system tables like STV_BLOCKLIST to estimate row counts faster (but note this is an approximation).

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