Re: Automate copy - Postgres 9.2

2016-06-09 12:19 GMT+12:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.