Table variable not Recognized in stored procedure The following stored procedure for a postgresql database attempts to insert into a temporary table 'result' of type result_load_status ,which is a table in database, and return this temporary table. When function is executed, the output window shows a message 'result does not exist'.
How to fix it or any other way it should be done?

Following is the definition of stored procedure:

create or replace function trial5(table_name text)
returns table(up_link double precision,time_stamp timestamp without time zone) as $$
declare b integer default 1;
declare c integer default 2;
declare result result_load_status;
begin
execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || b into result;
execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || c into result;

return query select * from result;
end

$$ language plpgsql;


Error Message:


  ERROR:  relation "result" does not exist
  LINE 1: select * from result
There are quite a few problems with this code:


Consecutive calls of select ... into result do no accumulate rows into result, each one overwrites whatever was previously in result.
A variable typed after a table (result of type result_load_status here) is meant to contain only one row of this type, not an entire resultset. For an entire resultset, use CREATE TEMPORARY TABLE or a cursor if possible.
select * from name_of_a_variable does not exist as a valid construct. A temporary table instead of a variable's name would work.


Anyway since accumulating results is already built in RETURN QUERY, you don't need any of the above.

Quoting from the documentation:


  RETURN QUERY appends the results of executing a query to the
  function's result set


So you may just write:

RETURN QUERY execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || b;

RETURN QUERY execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || c;


without any need for the problematic result variable.