2016-06-09 12:19 GMT+12:00 Adrian Klaver <adrian.kla...@aklaver.com>:
> 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.
