Analog for table-variable in postgres

I'm wrighting a stored function and I need to have table-variable like in MSSQL. I need to have possibility to insert/read/delete records from this table and use it in queries.

Temp tables are not suitable for me, because if I run this function simultaneously several times - same temp table will be used in all calls and I'll receive total mess there. Wrapping the body of function in transaction is not an option for me - it will block other users.

So what can I use instead ?

Here is simplified version of what I'm doing:

CREATE OR REPLACE FUNCTION public.DoSomeCoolStuff(iterations integer)
RETURNS Table(
    Id1 uuid,
    Id1 uuid,
    Id1 uuid) AS $$
#variable_conflict use_column
BEGIN
    CREATE TEMP TABLE intermediate_table (Id uuid) ON COMMIT DROP;
    INSERT INTO intermediate_table Select ...complex query
    ...and some more temp tables
    

    FOR i IN 1..iterations
    LOOP
        ... Some calculations...
        DELETE FROMM intermediate_table WHERE...previous calculations...
        ...
    END LOOP;

RETURN QUERY
    SELECT * FROM intermediate_table INNER JOIN Products On ...

END; $$
LANGUAGE plpgsql;

When I call this function multiple times simultaneously - postgres says

relation "intermediate_table" already exists

So I assume, that all instances of function calls see the same temp table.

All queries are executed by one user - our backend server.

Solution

The relation "intermediate_table" already exists error could be because you're not explicitly dropping it and keep a transaction open between calls, avoiding the on commit drop event, or there's something else in your search_path that's already using that name.

If you want to keep it around change the CREATE to CREATE..IF NOT EXISTS then wipe it at the end with TRUNCATE or DELETE. If not, it's good practice to explicitly DROP TABLE intermediate_table; without trusting the caller will stick to one call per transaction.

If you had a problem droppping or wiping it right at the end of the function because you need return stuff from the table first, note that return query and return next don't immediately terminate the function, so you can do your cleanup after that. Quoting the doc:

individual items to return are specified by a sequence of RETURN NEXT or RETURN QUERY commands, and then a final RETURN command with no argument is used to indicate that the function has finished executing.

Your other concerns:

Temp tables are not suitable for me, because if I run this function simultaneously several times - same temp table will be used in all calls and I'll receive total mess there. Wrapping the body of function in transaction is not an option for me - it will block other users.

One user in one session can't run the function "simultaneously several times", only in sequence. Each concurrent user that calls the function in parallel, from their own session, will get a separate temp table of their own, so they will neither block nor interfere with each other.

Even if you call this function multiple times in a single query:

select * 
from       public.DoSomeCoolStuff(1) as call1
cross join public.DoSomeCoolStuff(2) as call2
cross join public.DoSomeCoolStuff(3) as call3;

they will get evaluated in sequence, unless you somehow convince Postgres the function is parallel safe (despite it being clearly not the case), and the planner finds it a good idea to evaluate these calls in parallel. Clean it up at the end, and subsequent calls will be able to re-use it without a problem.

One scenario you need to be worried about is calling it recursively: each nested call will see rows from the upper call levels. In that case, you can simply add a column to uniquely identify your calls to avoid the interference: _{demo at db<>fiddle}

CREATE OR REPLACE FUNCTION public.DoSomeCoolStuff(iterations integer)
RETURNS Table(
    Id1 uuid,
    Id1 uuid,
    Id1 uuid) AS $f$
#variable_conflict use_column
DECLARE v_call_id uuid:=gen_random_uuid();
BEGIN
    CREATE TEMP TABLE IF NOT EXISTS intermediate_table
        (call_id uuid, Id uuid) ON COMMIT DROP;
    INSERT INTO intermediate_table SELECT v_call_id, ...complex query
    ...and some more temp tables
    
    FOR i IN 1..iterations
    LOOP
        ... Some calculations...
        DELETE FROM intermediate_table 
        WHERE call_id = v_call_id 
          AND ...previous calculations...
        ...
    END LOOP;
RETURN QUERY
    WITH wipe_and_return AS (
       DELETE FROM intermediate_table 
       WHERE call_id = v_call_id
       RETURNING *)
    SELECT * 
    FROM wipe_and_return
    INNER JOIN Products 
            ...
END; $f$
LANGUAGE plpgsql;