2016-06-09 12:19 GMT+12:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:
On 06/08/2016 04:24 PM, Patrick B wrote:
Hi guys,
I need to do a file backup for each account_id.
Example:
|COPY (SELECT*FROMbackup_table WHEREid =1112ANDstatus
=1)TO'/var/lib/pgsql/1112.sql';COPY (SELECT*FROMbackup_table WHEREid
=1113ANDstatus =1)TO'/var/lib/pgsql/1113.sql';COPY
(SELECT*FROMbackup_table WHEREid =1114ANDstatus
=1)TO'/var/lib/pgsql/1114.sql';|
Can I create a PLPGSQL function to perform that?
I tried but isn't working... don't know how to determinate that:
Define not working.
Don't know how to determinate that for each id must have a different file.
FYI, COPY:
https://www.postgresql.org/docs/9.2/static/sql-copy.html
Notes
"Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client. COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access."
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 gorfs.WBP_4868_backup_table_BLOBS WHERE
migrated = 1 AND account_id = crtRow.account_id)
TO '/var/lib/pgsql/gorfs_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
Unclear.
Single id as you show, a range of numbers or an array of numbers?
select function(21);
Where 21 = Number of ids
Can anybody give me a help here please?
You will get better help quicker if you are clearer in your problem description and include illustrative examples of what you want to achieve.
I already have:
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'; That's what I want.. but i don't wanna do that manually...
I need a separate file for each id.
