3

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?
2

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.