Table variable not Recognized in stored procedure

Question

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

Answer 1

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.