Postgres - Why is the temporary table created in one function undefined in another function?-CodePudding

this is a Postgres related question.

I can't figure out why I get ERROR undefined_table relation "temp_table" does not exist

I tried all sorts of things, but maybe I don't really understand how I correctly use a temporary table in a function that was created in another function.

CREATE FUNCTION api.test() RETURNS BOOLEAN LANGUAGE 'plpgsql' SECURITY DEFINER AS $$
BEGIN
    CREATE TEMP TABLE temp_table( id INT NOT NULL) ON COMMIT DROP;
    INSERT INTO temp_table (id) VALUES (1), (2);
    RETURN TRUE;
END;
$$;

CREATE FUNCTION api.test_id(p_id INT) RETURNS BOOLEAN LANGUAGE 'plpgsql' SECURITY DEFINER AS $$
DECLARE
    v_id INTEGER;
BEGIN
    SELECT id INTO v_id FROM temp_table WHERE id = p_id LIMIT 1;
    return v_id IS NOT NULL;
END;
$$;

PS: I create the temporary table in a function to protect it from being accessed outside of the functions by a different role.

CodePudding user response：

I tested your code. It works if in the same transaction.

You are working from different transactions. See here: https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY

At "Temporary Tables"