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