1

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

2

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.

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.