patternsqlMinor
Make a temporary table visible for a function inside a function (or a subfunction)
Viewed 0 times
visibletemporarysubfunctionmakefunctionfortableinside
Problem
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).
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.
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 ERRORThere 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.
Solution
You can pass the name of the table:
See it working at: http://rextester.com/FYMX28296
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
Code Snippets
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();Context
StackExchange Database Administrators Q#183769, answer score: 2
Revisions (0)
No revisions yet.