PL/pgSQL - Using function argument in Loop

Question

I'm new to PL/pgSQL and any help is highly appreciated.

I'm trying to create a function to copy data from a list of tables(table names stored in table "meta1") to the archive tables based on "cren_date" column in the tables. I'm passing the argument as number of days of data to be moved from the current date.

create or replace function schema.data_copy(IN tar_date int )
returns void as
$body$
declare
rec text;
begin
    For rec in (select candidate from schema.meta1)
    LOOP
        EXECUTE format 'insert into schema.' ||rec|| '_ar select * from schema.' || rec|| ' where cren_date<= current_date- ||tar_date||;';
    END LOOP;
end;
$body$
language plpgsql;

I'm getting the following error:

ERROR:  type "format" does not exist
LINE 1: SELECT format 'insert into schema.' ||rec|| '_ar select * f...
               ^
QUERY:  SELECT format 'insert into schema.' ||rec|| '_ar select * from schema.' || rec|| ' where cren_date<= current_date- ||tar_date||;'
CONTEXT:  PL/pgSQL function schema.data_copy(integer) line 7 at EXECUTE
********** Error **********

ERROR: type "format" does not exist
SQL state: 42704
Context: PL/pgSQL function schema.data_copy_3(integer) line 7 at EXECUTE

I have two issues - 1. The error listed above 2. How to use argument tar_date in the Execute statement?

Thank You

Avocado · Accepted Answer · 2020-06-10 23:21:36Z

2

Wrap the argument to format in parentheses, and use %s to interpolate arguments.

create or replace function schema.data_copy(IN tar_date int )
returns void as
$body$
declare
rec text;
begin
    For rec in (select candidate from schema.meta1)
    LOOP
        EXECUTE format(
          'insert into schema.%s_ar
           select * from schema.%s
           where cren_date <= current_date-%s;
          ', rec, rec, tar_date
        );
    END LOOP;
end;
$body$
language plpgsql;

answered Jun 10, 2020 at 23:21

Avocado

9117 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Patz14 Over a year ago

Thank you so much for the help. I'm able to execute the function and populate the archive tables. One issue is - The data is being populated in source table as well(dupicated). I know primary keys can be implemented but why is it genrating insert statements for Source tables?

Belayer Over a year ago

Have you checked that the candidate column values are unique. It not you copy the duplicate tables twice (or more).

Collectives™ on Stack Overflow

PL/pgSQL - Using function argument in Loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related