CODEWITHSUNDEEP
postgresqlfunctionloopsfor-looparguments
Patz14
Patz14

Reputation: 5

PL/pgSQL - Using function argument in Loop

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

Upvotes: 0

Views: 316

Answers (1)

Avocado
Avocado

Reputation: 901

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;

Upvotes: 2

Related Questions