How to fix trigger cannot change partition destination in PostgreSQL

SELECT 
  schemaname, tablename, 
  pg_get_partition_partstr(oid) as partition_by
FROM pg_tables t
JOIN pg_class c ON c.relname = t.tablename
JOIN pg_namespace n ON n.oid = c.relnamespace 
  AND n.nspname = t.schemaname
WHERE t.tablename = 'your_partitioned_table';

SELECT pg_get_triggerdef(oid) 
FROM pg_trigger 
WHERE tgname = 'your_trigger_name';

NEW.created_at := NOW();  -- WRONG: modifies partition key

-- BEFORE: Wrong (modifies partition key)
CREATE OR REPLACE FUNCTION trigger_set_date()
RETURNS TRIGGER AS $$
BEGIN
  NEW.created_at := NOW();  -- ERROR: partition key
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- AFTER: Correct (doesn't touch partition key)
CREATE OR REPLACE FUNCTION trigger_audit_log()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (action, record_id, changed_at) 
  VALUES ('INSERT', NEW.id, NOW());
  RETURN NEW;  -- Partition key unchanged
END;
$$ LANGUAGE plpgsql;

# Python example
import psycopg2
from datetime import datetime

# Calculate partition key BEFORE insert
created_at = datetime.now()
record_id = generate_id()

# Insert with pre-set partition key
cursor.execute(
    "INSERT INTO partitioned_table (id, created_at, data) VALUES (%s, %s, %s)",
    (record_id, created_at, "some data")
)

-- Staging table (non-partitioned)
CREATE TABLE staging_data (
  id BIGSERIAL PRIMARY KEY,
  original_date DATE,
  adjusted_date DATE,
  data TEXT
);

-- Partitioned target table
CREATE TABLE partitioned_data (
  id BIGSERIAL PRIMARY KEY,
  created_at DATE,
  data TEXT
) PARTITION BY RANGE (created_at);

-- Trigger on staging table can modify freely
CREATE OR REPLACE FUNCTION stage_to_partition()
RETURNS TRIGGER AS $$
BEGIN
  -- Transform data (including date adjustments) in staging
  NEW.adjusted_date := COALESCE(NEW.original_date, NOW()::DATE);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Separate process moves finalized data to partitioned table
INSERT INTO partitioned_data (created_at, data)
SELECT adjusted_date, data FROM staging_data
WHERE processed = false;

INSERT INTO your_partitioned_table (id, created_at, other_col) 
VALUES (1, '2024-01-15', 'test data');