Copy to clipboard | Top
CREATE PROCEDURE Archivedata() LANGUAGE PLPGSQL AS $$
Declare count int;
declare maxcount int;
declare fulltablename varchar(100);
declare ptablename varchar(100);
declare bucketname varchar(100);
BEGIN

DROP TABLE IF EXISTS detachedtable;
Create temporary table DetachedTable(tableName varchar(200),rankk int);
Insert into DetachedTable(tableName,rankk)
select relname,row_number() over(order by relname ASC) as rankk from pg_class
join pg_namespace n on n.oid = relnamespace where relkind = 'r' and relispartition ='f'
and relname like '%_p20%' and n.nspname = 'dbo';

count:=1;
Select count(1) as tablecount into maxcount from DetachedTable;

raise notice 'Total no of table is =%',maxcount;
    while (count<=maxcount) loop
    select 'dbo.'||tableName into fulltablename from DetachedTable where rankk=count;
    select tableName into ptablename from DetachedTable where rankk=count;
    select substring(tableName,1,(position('_p20' in tableName)-1)) into bucketname from DetachedTable where rankk=count;
        SELECT * FROM aws_s3.query_export_to_s3('Select * from '|| fulltablename,
aws_commons.create_s3_uri(
'archive-bucket',
bucketname||'/'||ptablename,'us-east-2'));

    count:=count+1;
    end loop;
END;$$;

Above stored procedure is throwing error while running,
and the ERROR is

ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function archivedata() line 30 at SQL statement

SQL state: 42601

I’m attempting to archive data using pg_partman. This stored procedure aims to identify detached partitions and transfer their data to an S3 bucket. It doesn’t involve selecting any specific data; it simply moves the entire partition to S3 using the aws_s3.query_export_to_s3 function.

We have multiple tables, and the procedure needs to dynamically determine the appropriate bucket name and folder for each table. As I’m new to PostgreSQL and AWS, I’m seeking guidance on resolving this issue.

Is a stored procedure the most suitable approach for this task, or should I consider using functions instead?