如何创建支持多表多字段的通用PostgreSQL标准化ID触发器函数

Great question! Instead of cluttering your function with table-specific IF/ELSEIF checks, we can build a fully generic trigger function that adapts to any table by accepting the target column name as an argument when creating the trigger. This uses PostgreSQL's built-in trigger context variables and JSONB to dynamically modify the correct column without hardcoding table logic.

Modified Generic Function #

Here's the updated function that works with any table and text-based column:

CREATE OR REPLACE FUNCTION std_user_id()
RETURNS trigger AS $$
DECLARE
    modified_value text;
    column_exists boolean;
BEGIN
    -- Ensure a column name was provided when creating the trigger
    IF TG_ARGV[0] IS NULL THEN
        RAISE EXCEPTION 'Trigger requires a column name as an argument (e.g., EXECUTE FUNCTION std_user_id(''login_id''))';
    END IF;

    -- Validate the column exists in the target table (optional but recommended)
    SELECT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = TG_TABLE_SCHEMA
          AND table_name = TG_TABLE_NAME
          AND column_name = TG_ARGV[0]
    ) INTO column_exists;

    IF NOT column_exists THEN
        RAISE EXCEPTION 'Column "%" does not exist in table "%"."%"', TG_ARGV[0], TG_TABLE_SCHEMA, TG_TABLE_NAME;
    END IF;

    -- Trim and uppercase the column value
    modified_value := UPPER(TRIM(to_jsonb(NEW) ->> TG_ARGV[0]));

    -- Update the NEW record with the modified value (works for any table type)
    NEW := jsonb_set(to_jsonb(NEW), ARRAY[TG_ARGV[0]], to_jsonb(modified_value))::pg_typeof(NEW);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

How to Use It for Different Tables #

When creating triggers, simply pass the name of the column you want to normalize as an argument to the function:

For the users table (targeting login_id): #

DROP TRIGGER IF EXISTS users_std_user_id ON users;
CREATE TRIGGER users_std_user_id
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION std_user_id('login_id');

For the user_roles table (targeting user_id): #

DROP TRIGGER IF EXISTS user_roles_std_user_id ON user_roles;
CREATE TRIGGER user_roles_std_user_id
BEFORE INSERT OR UPDATE ON user_roles
FOR EACH ROW
EXECUTE FUNCTION std_user_id('user_id');

Key Benefits #

• No table-specific logic: Works with any table that has a text-based column you want to normalize.
• Flexible: Add triggers for new tables by just specifying the column name—no function changes needed.
• Safe: Includes validation to ensure the specified column exists, preventing unexpected runtime errors.

Notes #

• This assumes the target column is a text-based type (like text, varchar). For non-text columns, you’d need to add a type check to avoid conversion errors.
• The JSONB approach is clean and avoids complex dynamic SQL, making the function easy to maintain and debug.

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