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

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;

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?
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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related