PostgreSQL 2F004 Error: Causes and Solutions Complete Guide

PostgreSQL Error 2F004: Reading SQL Data Not Permitted

PostgreSQL error code 2F004 (reading sql data not permitted) occurs when a function declared with restrictive SQL data access attributes (such as NO SQL or CONTAINS SQL) attempts to execute a SELECT statement or read data from a table. This is a SQL routine exception that PostgreSQL enforces at runtime to ensure functions behave consistently with their declared properties. Understanding the mismatch between a function's declared attributes and its actual behavior is the key to resolving this error.

---

Top 3 Causes

1. Function Declared with NO SQL but Contains a SELECT Statement

When you declare a function with NO SQL, PostgreSQL assumes it will never execute any SQL. If the function body includes a SELECT query, the error is raised at execution time.

-- Problematic function
CREATE OR REPLACE FUNCTION get_user_email(p_id INT)
RETURNS TEXT
LANGUAGE plpgsql
NO SQL  -- Incorrect: function actually reads data
AS $$
DECLARE
    v_email TEXT;
BEGIN
    SELECT email INTO v_email
    FROM users
    WHERE id = p_id;
    RETURN v_email;
END;
$$;
-- Calling this function raises: ERROR: 2F004 reading sql data not permitted

-- Fixed version
CREATE OR REPLACE FUNCTION get_user_email(p_id INT)
RETURNS TEXT
LANGUAGE plpgsql
READS SQL DATA  -- Correct attribute
AS $$
DECLARE
    v_email TEXT;
BEGIN
    SELECT email INTO v_email
    FROM users
    WHERE id = p_id;
    RETURN v_email;
END;
$$;

---

2. CONTAINS SQL Used Instead of READS SQL DATA

CONTAINS SQL means the function has SQL statements but does not read or modify table data. Using SELECT inside such a function triggers 2F004.

-- Wrong: CONTAINS SQL does not permit reading table data
CREATE OR REPLACE FUNCTION total_orders()
RETURNS BIGINT
LANGUAGE sql
CONTAINS SQL
AS $$
    SELECT COUNT(*) FROM orders;
$$;

-- Correct: Use READS SQL DATA when reading tables
CREATE OR REPLACE FUNCTION total_orders()
RETURNS BIGINT
LANGUAGE sql
READS SQL DATA
AS $$
    SELECT COUNT(*) FROM orders;
$$;

---

3. Third-Party or Custom C Functions with Wrong Attributes

Custom C functions or Foreign Data Wrapper functions sometimes get registered with NO SQL by mistake, even though they internally read data.

-- Check existing function attributes
SELECT proname, provolatile, prosrc
FROM pg_proc
WHERE proname = 'my_custom_func';

-- Re-register with correct attribute
DROP FUNCTION IF EXISTS my_custom_func(INT);

CREATE OR REPLACE FUNCTION my_custom_func(p_id INT)
RETURNS TEXT
LANGUAGE plpgsql
READS SQL DATA
AS $$
DECLARE
    v_val TEXT;
BEGIN
    SELECT col INTO v_val FROM my_table WHERE id = p_id;
    RETURN v_val;
END;
$$;

---

Quick Fix Solutions

• Replace NO SQL with READS SQL DATA if your function reads table data.
• Replace CONTAINS SQL with READS SQL DATA when a SELECT statement is present.
• Use MODIFIES SQL DATA if the function performs INSERT, UPDATE, or DELETE.
• If you're unsure, omit the attribute entirely for PL/pgSQL — PostgreSQL will apply the default behavior.

-- Audit query: find suspicious functions with NO SQL but containing SELECT
SELECT
    n.nspname AS schema_name,
    p.proname AS function_name
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND pg_get_functiondef(p.oid) ILIKE '%NO SQL%'
  AND pg_get_functiondef(p.oid) ILIKE '%SELECT%';

---

Prevention Tips

1. Always explicitly declare SQL data access attributes and include them in code reviews. Establish a team convention to verify that the declared attribute matches the actual function body before deployment.

2. Run periodic audits using the query above (or schedule it via pg_cron) to catch any functions where the declared attribute conflicts with the function body. Catching these mismatches early prevents runtime surprises in production.

---

Related Errors

• 2F000 — General SQL routine exception (parent class of 2F004)
• 2F002 — modifying_sql_data_not_permitted: triggered when a READS SQL DATA function tries to write data
• 2F003 — prohibited_sql_statement_attempted: disallowed SQL executed in a restricted context
• 42501 — insufficient_privilege: often confused with 2F004, but caused by missing user permissions rather than function attribute mismatches

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.