Postgresql – Automate COPY command – Postgres 9.2

backupcopypostgresqlpostgresql-9.2

I've a backup table with several distinct IDS… I wanna do a BACKUP of those rows, by ID.

  • The function has to be executed by a limit of account_id ( select dump(21); – Where 21 = number of account_id to be touched by the function )

  • Each account_id must have a different file name. Examples below:

Example:

COPY 
(
SELECT * FROM backup_table WHERE id = 1112 AND status = 1
) 
TO '/var/lib/pgsql/1112.sql';

COPY 
(
SELECT * FROM backup_table WHERE id = 1113 AND status = 1
) 
TO '/var/lib/pgsql/1113.sql';

COPY 
(
SELECT * FROM backup_table WHERE id = 1114 AND status = 1
) 
TO '/var/lib/pgsql/1114.sql';

Question:

Can I create maybe a function to do that automatically? There are thousands of those IDS and would take ages by doing manually.

UPDATE 1:

Tried to do:

CREATE or REPLACE FUNCTION dump(integer)
RETURNS integer AS $$

declare
   crtRow record;
begin
      FOR crtRow in execute 'select account_id from backup_table WHERE migrated = 1 AND account_id = '|| $1

      LOOP
    COPY
        (SELECT * FROM backup_table WHERE migrated = 1 AND account_id = crtRow.account_id)
    TO '/var/lib/pgsql/backup/%s.sql';
    end loop;
    return integer;
end

$$ language 'plpgsql';
  • Each account_Id would have a file with its ID
  • When calling the function, I want to specify the numbers of account_Ids I wanna do the dump

However it isn't working…

UPDATE 2:

CREATE or REPLACE FUNCTION function_1(rows integer)
RETURNS void AS $$

declare
   crtRow record;
begin
      FOR crtRow in EXECUTE 'select DISTINCT(account_id) from backup_table WHERE migrated = 1 AND account_id IN '|| $1

      LOOP
    COPY
        (SELECT * FROM backup_table WHERE migrated = 1 AND account_id = crtRow.account_id)
    TO '/var/lib/pgsql/' || crtrow.account_id || '.csv';
    end loop;
end

$$ language 'plpgsql';

Error:

ERROR: syntax error at or near "||" LINE 12: TO '/var/lib/pgsql/'
|| crtrow.account_id |…

UPDATE 3: amacvar's answer

CREATE or REPLACE FUNCTION function_1(rows integer)
RETURNS void AS $$

begin
execute 'COPY
(
SELECT * FROM backup_table WHERE id = ' || rows || 'AND status = 1
)
TO ''/var/lib/pgsql/'||rows||'.sql''';

end

$$ language 'plpgsql';

The above function based on the amacvar's answer works. The problem is that I have to specify the id number…

Example: 

`select function_1(25)` - Where 25 = id

That isn't good for me, as I have thousands of ids and can't specify them manually.

What I need is:

The same function above, but with a LIMIT of IDS to be specified when
the function is called:

EXAMPLE:

select function_1(100) – WHERE 100 = numbers of id to be copied

  • How can I do that?

Best Answer

The scripting answers above will obviously work.

But, assuming you have to, have to, have it executed from within the DB (let us know why :) ), the code below (minus the loop, etc.) should get you on your way.

You were very close btw.

CREATE or REPLACE FUNCTION function_1(rows text)
RETURNS void AS $$
begin
  execute 'COPY (
    SELECT * 
      FROM backup_table
     WHERE id = ' || rows || ' 
       AND status = 1
  ) TO ''/var/lib/pgsql/'||rows||'.dat''';
end
$$ language 'plpgsql';

EDIT 1: function loop example and formatting

CREATE or REPLACE FUNCTION function_loop(rows_arr text[])
RETURNS void AS $$
declare
  crtRow text;
begin
  ForEach crtRow in array rows_arr
  LOOP
     perform function_1(crtRow);  
  end loop;
end
$$ language 'plpgsql';

Executed as

select function_loop('{1,2,3}');

Please note there are no checks being performed here.

E.g. This will create a zero byte 3.dat even if there is no id=3 in the table

EDIT 2: A more extensible option for thousands of id would be

CREATE or REPLACE FUNCTION function_loop1(startId int, endId int)
RETURNS void AS $$
declare
  allIds text[];
  crtRow text;
begin
  select array_agg(id::text) into allIds 
    from backup_table
   where status=1 
     and id between startId and endId;
  ForEach crtRow in array allIds
  LOOP
    perform function_1(crtRow);  
  end loop;
end
$$ language 'plpgsql';
Related Question