How to fix 39P02: srf_protocol_violated in PostgreSQL

-- Check the full error context
-- Example error:
-- ERROR: table-function protocol for materialize mode was not followed
-- CONTEXT: SQL function "list_postgres_log_files"

-- Identify if it's a system function or custom function
SELECT proname, prosrc, prorettype, proretset
FROM pg_proc
WHERE proname = 'your_function_name';

-- For admin functions like list_postgres_log_files()
DROP EXTENSION IF EXISTS adminpack CASCADE;
CREATE EXTENSION adminpack;

-- For custom extensions
DROP EXTENSION IF EXISTS your_extension CASCADE;
CREATE EXTENSION your_extension;

-- If using foreign data wrappers
DROP EXTENSION IF EXISTS postgres_fdw CASCADE;
CREATE EXTENSION postgres_fdw;

SELECT dependent.relname
FROM pg_depend
JOIN pg_extension ON pg_depend.refobjid = pg_extension.oid
JOIN pg_class dependent ON pg_depend.objid = dependent.oid
WHERE pg_extension.extname = 'your_extension';

CREATE OR REPLACE FUNCTION get_users()
RETURNS SETOF users AS $$
BEGIN
    RETURN QUERY SELECT * FROM users WHERE active = true;
    -- Missing final RETURN to signal completion
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_users()
RETURNS SETOF users AS $$
BEGIN
    RETURN QUERY SELECT * FROM users WHERE active = true;
    RETURN;  -- Signal function completion
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION conditional_rows(limit_rows BOOLEAN)
RETURNS SETOF integer AS $$
BEGIN
    IF limit_rows THEN
        RETURN QUERY SELECT generate_series(1, 10);
    ELSE
        RETURN QUERY SELECT generate_series(1, 100);
    END IF;
    RETURN;  -- Always end with RETURN
END;
$$ LANGUAGE plpgsql;

#include "postgres.h"
#include "funcapi.h"

Datum
my_table_function(PG_FUNCTION_ARGS)
{
    FuncCallContext *funcctx;
    ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;

    // Check if caller supports materialize mode
    if (!rsinfo || !(rsinfo->allowedModes & SFRM_Materialize))
        ereport(ERROR,
            (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
             errmsg("materialize mode required, but not supported in this context")));

    if (SRF_IS_FIRSTCALL())
    {
        funcctx = SRF_FIRSTCALL_INIT();
        // Initialize result tuple descriptor
        // Build tuplestore for materialized results
    }

    funcctx = SRF_PERCALL_SETUP();

    // Return rows...

    SRF_RETURN_DONE(funcctx);
}

-- SRF in SELECT clause (deprecated in PostgreSQL 10+)
SELECT id, generate_series(1, 5) FROM users;

-- Better: Use LATERAL join
SELECT u.id, g.n
FROM users u
CROSS JOIN LATERAL generate_series(1, 5) AS g(n);

-- Verify function signature
SELECT
    proname,
    pg_get_function_result(oid) AS return_type,
    proretset AS returns_set
FROM pg_proc
WHERE proname = 'your_function';

-- Ensure calling query expects set-returning results

-- Simple test function
CREATE OR REPLACE FUNCTION test_srf()
RETURNS SETOF integer AS $$
BEGIN
    RETURN QUERY SELECT generate_series(1, 3);
    RETURN;
END;
$$ LANGUAGE plpgsql;

-- Test in different contexts
SELECT * FROM test_srf();  -- Should work
SELECT test_srf();         -- Also should work

-- If test function works but original doesn't, compare implementations

CREATE TYPE test_result AS (id INT, name TEXT);

CREATE OR REPLACE FUNCTION test_table_func()
RETURNS SETOF test_result AS $$
BEGIN
    RETURN QUERY SELECT 1, 'first'::TEXT;
    RETURN QUERY SELECT 2, 'second'::TEXT;
    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM test_table_func();