Postgresql – Make a temporary table visible for a function inside a function (or a subfunction)

plpgsqlpostgresqltemporary-tables

I have two functions in PL/pgSQL:

The function 1 calls function 2, and function 2 needs to process a temporary table created on function 1, in another words, this temporary table needs to be global in the function 1 context (that have function 2 inside it).

FUNCTION1 
   | CREATE TEMPORARY TABLE
   | DO SOME SPECIFIC STUFF...
   | FUNCTION2
         | DO SOME GENERIC STUFF
         | INSERT SOME DATA INTO THE TEMPORARY TABLE  
         | (Uhh!! Ohh! it hurts, table not visible)
   | RAISED ON ERROR

There is a way to pass the temporary table created on function 1 to this subfunction?

I need it because i am working with a very big function that process a lot of data in a temporary table, before inserting all the big data, but when i try to turn into a more modular form, the subfunctions just can't see the temporary table).

It is working by now (in a big one function), but i am doing CTRL+C CTRL+V to create new similar functions, as a lot of codes can be repeated inside it. If i can use subfunctions this problem will go out, as i can use this code multiple times.

Best Answer

You can pass the name of the table:

create or replace function f2(p_table_name text) returns text
  language plpgsql
as
$body$
begin
  execute $$insert into $$||quote_ident(p_table_name)||$$ (c1) values ('text1');$$;
  return 'ok';
end;
$body$;

create or replace function f1() returns text
  language plpgsql
as
$body$
declare 
  v1 text;
begin
  create temporary table t1 (
    c1 text
  );
  perform f2('t1');
  select c1 into v1 from t1 limit 1;
  return v1;
end;
$body$;

select f1();

See it working at: http://rextester.com/FYMX28296

Related Question