2

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.