Automate COPY command - Postgres 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 REPL

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';

StackExchange Database Administrators Q#140786, answer score: 3