Creating TEMP TABLE dynamically in Postgresql and selecting the same table in FOR loop. But getting the error near PIPE symbol

Question

do
$xyz$
declare
y text;
i record;
begin
y := to_char(current_timestamp, 'YYYYMMDDHHMMSS');
raise notice '%',y;
execute 'CREATE TEMP TABLE someNewTable' 
  ||y
  ||' AS select * from ( VALUES(0::int,-99999::numeric), (1::int,       100::numeric)) as t (key, value)';

    for i in (select * from someNewTable||y) loop
     raise notice '%',i.key;
    end loop;
   end;
  $xyz$ language 'plpgsql'


 ERROR:  syntax error at or near "||"
LINE 13:    for i in (select * from someNewTable||y) loop

Im unable to understand why the error is at the PIPE symbol. Please help me. I have been trying in Oracle db too, but same error. Am I doing anything wrong here?

Answer 1

The query in for ... loop statement also has to be dynamic, so you should use execute twice.

Use the format() function which is very convenient in conjunction with execute:

do $xyz$
declare
    y text;
    i record;
begin
    y := to_char(current_timestamp, 'YYYYMMDDHHMMSS');
    raise notice '%', y;
    execute format($ex$
        create temp table somenewtable%s
        as select * from (
            values
                (0::int, -99999::numeric), 
                (1::int, 100::numeric)
            ) as t (key, value)
        $ex$, y);

    for i in 
        execute format($ex$
            select * from somenewtable%s
            $ex$, y)
    loop
        raise notice '%',i.key;
    end loop;
end;
$xyz$ language 'plpgsql';