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.
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?
>
> 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.
--
Adrian Klaver
adrian.klaver@aklaver.com