CODEWITHSUNDEEP
sqlpostgresql
Miguel Pinto
Miguel Pinto

Reputation: 497

PostgreSQL - Shared temp table between functions

I wnat to know if its possible to share a temporary table between functions that are called in a "main function", like this:

-- some sub function
create or replace function up_sub_function (str text)
returns table (id int, descr text) as $$
begin

   return query select * from temp_table where descr like concat('%', str , '%');

end; $$
language plpgsql;

-- main function
create or replace function up_main_function ()
returns table (id int, descr text) as $$
begin
   create temporary table temp_table if not exists (
      id int,
      descr text
   );

   insert into temp_campaigns select id, descr from test_table;

   return query select * from up_sub_function('a');

end; $$
language plpgsql;


BEGIN;
   select * from up_main_function();
   drop table temp_table;
COMMIT;

If you can show me the correct way to achieve this, I want to be able to populate a temporary table and then filter rows by calling othe functions inside the main function.

Thanks ans happy programming! :)

Upvotes: 1

Views: 1312

Answers (1)

Martin
Martin

Reputation: 541

See the documentation https://www.postgresql.org/docs/current/static/sql-createtable.html

temp tables are valid for the entire session. That is as long as you stay connected to the database.

In your case you only need it during the transaction. So you should create it with ON COMMIT DROP

create temporary table temp_table if not exists (
   id int,
   descr text
) ON COMMIT DROP;

Once you created the table you can use it within any function in the current transaction.

You do not need the BEGIN to start the transaction. A transaction is automatically started when the outer function is called.

Nested function calls share the same transaction. So they all see the table.

Upvotes: 3

Related Questions