Automate COPY command - Postgres 9.2

Question

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?

Answer 1

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

Answer 2

You can try something like below in a bash script:

#!/bin/bash
su postgres
for id in `psql -d database -c"select DISTINCT(account_id) from backup_table WHERE migrated = 1 AND account_id IN ";` 
do
 psql -d database -c"COPY (SELECT * FROM backup_table WHERE id = "$id" AND status = 1) TO '/var/lib/pgsql/"$id".sql'";
done

Then run this in script through crontab.