Not able to create backup of table dynamically, through PL/pgSQL function

Question

I am trying to create a function to create table backup dynamically.

But I am getting error like :

ERROR: syntax error at or near "'

Here's one of my approach, which I am trying:

CREATE OR REPLACE FUNCTION public.test () RETURNS varchar AS
$BODY$ DECLARE backup_string varchar(50); 
BEGIN
backup_string = (SELECT '_'||LPAD(DATE_PART('DAY',CURRENT_DATE)::VARCHAR,2,'0')||DATE_PART('MONTH',CURRENT_DATE)::VARCHAR||DATE_PART('YEAR',CURRENT_DATE)::VARCHAR||'_1');

EXECUTE 'SELECT  * INTO table_name'|| backup_string ||' FROM table_name';

RETURN 'Y';
EXCEPTION WHEN others THEN RETURN 'N'; 
END
    ; $BODY$
LANGUAGE 'plpgsql'
GO
SELECT * FROM test()

I am not getting, why that execute statement giving me error like that.

Answer 1

I suggest so simplify your code and make use of the format() function to generate the dynamic SQL. That way you can avoid the clutter that concatenation generates and you can concentrate on the actual SQL code. In addition to that it also properly deals with identifiers that might need quoting.

When dealing with dynamic SQL it's always a good idea to store the generated SQL statement in a variable, so that it can be printed for debugging purposes if you get an error. Looking at the generated SQL usually tells you where the generation code went wrong.

CREATE OR REPLACE FUNCTION test() 
  RETURNS varchar 
AS
$BODY$ 
DECLARE 
  l_source_table text;
  l_backup_table text; 
  l_sql text;
BEGIN
  l_source_table := 'table_name';
  l_backup_table := l_source_table||'_'||to_char(current_date, 'ddmmyyyy')||'_1';
  l_sql := format('create table %I as select * from %I', l_backup_table, l_source_table);

  -- for debugging purposes:
  raise notice 'Running: %', l_sql

  EXECUTE l_sql;
  RETURN 'Y';
EXCEPTION 
  WHEN others THEN RETURN 'N'; 
END; 
$BODY$
LANGUAGE plpgsql;

Note that I also used variables for the source and backup table to be able to use that as a place holder for the format() function.

Online example