How to fix 2F003: prohibited_sql_statement_attempted in PostgreSQL

-- View the function source code
SELECT proname, prosrc
FROM pg_proc
WHERE proname = 'your_function_name';

-- Or use psql command
\df+ your_function_name

-- Instead of a function like this:
-- CREATE FUNCTION process_orders() RETURNS void AS $$
--   BEGIN
--     INSERT INTO orders_processed ...
--     COMMIT;  -- This causes 2F003 error
--   END;
-- $$ LANGUAGE plpgsql;

-- Use a PROCEDURE:
CREATE OR REPLACE PROCEDURE process_orders()
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO orders_processed (order_id, processed_at)
  SELECT order_id, NOW()
  FROM pending_orders;

  COMMIT;  -- Allowed in procedures

  DELETE FROM pending_orders WHERE processed = true;
  COMMIT;
END;
$$;

-- Call with CALL instead of SELECT:
CALL process_orders();

-- If DDL is necessary, use VOLATILE
CREATE OR REPLACE FUNCTION create_user_table(username text)
RETURNS void
LANGUAGE plpgsql
VOLATILE  -- Required for DDL operations
AS $$
BEGIN
  EXECUTE format('CREATE TABLE IF NOT EXISTS %I (
    id SERIAL PRIMARY KEY,
    data jsonb
  )', username || '_data');
END;
$$;

CREATE OR REPLACE FUNCTION safe_insert_order(order_data jsonb)
RETURNS integer
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
  new_order_id integer;
BEGIN
  INSERT INTO orders (data, created_at)
  VALUES (order_data, NOW())
  RETURNING id INTO new_order_id;

  RETURN new_order_id;
EXCEPTION
  WHEN unique_violation THEN
    -- PostgreSQL automatically rolls back the failed statement
    -- No explicit ROLLBACK needed
    RAISE NOTICE 'Duplicate order detected';
    RETURN NULL;
  WHEN OTHERS THEN
    -- Log error and re-raise
    RAISE WARNING 'Order insert failed: %', SQLERRM;
    RAISE;
END;
$$;

-- Don't do this:
-- CREATE FUNCTION maintenance() RETURNS void AS $$
--   BEGIN
--     VACUUM orders;  -- Causes 2F003 error
--   END;
-- $$ LANGUAGE plpgsql;

-- Instead, create a separate maintenance script:
-- maintenance.sql
VACUUM ANALYZE orders;
REINDEX TABLE orders;

-- Or use a DO block for one-time operations:
DO $$
BEGIN
  RAISE NOTICE 'Starting maintenance...';
  -- Regular SQL statements only, no VACUUM/ANALYZE
END $$;

-- For procedures, use CALL:
CALL process_orders();

-- For functions, use SELECT:
SELECT safe_insert_order('{"customer": "John", "total": 99.99}'::jsonb);

-- Verify function characteristics:
SELECT
  proname,
  prokind,  -- 'f' = function, 'p' = procedure
  provolatile,  -- 'v' = VOLATILE, 's' = STABLE, 'i' = IMMUTABLE
  prosrc
FROM pg_proc
WHERE proname = 'your_function_name';