PostgreSQL 9.6 – GRANT Requirements for Stored Procedure

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.

There is no such thing as a stored procedure in PostgreSQL. All PostgreSQL has are SQL-callable functions defined in a number of languages - LANGUAGE sql, LANGUAGE plpgsql, LANGUAGE plperl, etc.

Additionally, there's no RENAME TABLE command. You want ALTER TABLE ... RENAME TO ....

This particular example must be written in PL/PgSQL using EXECUTE because it relies on dynamic SQL. See the numerous existing examples on Stack Overflow of using PL/pgSQL EXECUTE. Something like a PL/PgSQL function with:

EXECUTE format('ALTER TABLE %I.%I RENAME TO %I.%I;',
    'FOOBAR_'||_schema_id, 'table1_org',
    'FOOBAR_'||_schema_id, 'table1_old');

Edit: OK, more complete example:

CREATE OR REPLACE FUNCTION myfunc(_schema_id integer)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    EXECUTE format('ALTER TABLE %I.%I RENAME TO %I.%I;',
        'FOOBAR_'||_schema_id, 'table1_org',
        'FOOBAR_'||_schema_id, 'table1_old');

    -- Now TRUNCATE the table and do your second `RENAME`
    -- in subsequent EXECUTE statements.
END;
$$;