CODEWITHSUNDEEP
postgresqlplpgsqlpostgresql-9.3postgresql-9.4
user1720827
user1720827

Reputation: 147

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

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?

Upvotes: 2

Views: 2586

Answers (1)

klin
klin

Reputation: 121889

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';

Upvotes: 4

Related Questions