Re: Automate copy - Postgres 9.2

2016-06-14 9:47 GMT+12:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 06/13/2016 02:42 PM, Patrick B wrote:
>
>> Hi guys,
>>
>> I created this function:
>>
>> CREATE or REPLACE FUNCTION function(account_id integer)
>>
>> RETURNS void AS $$
>>
>> begin
>>
>> execute 'COPY
>>
>> (
>>
>> SELECT * FROM backup_table WHERE account_id = ' ||
>> account_id || 'AND status = 1
>>
>> )
>>
>> TO ''/var/lib/pgsql/'||account_id||'.sql''';
>>
>> end
>>
>> $$ language 'plpgsql';
>>
>>
>>
>> The function works fine.. but is not what I need, actually.
>> The function above works by calling it specifying the account_id. For
>> example:
>>
>> You want to copy ( backup ) for the account_id number 63742:
>>
>> select function(63742);
>>
>>
>> *What I need is:*
>>
>> When calling the function, I have to specify the limit of account_ids to
>> be copied. For example:
>> To perform the commands in the function to 40 different account_ids:
>>
>> select function (40);
>>
>>
>>
>> How can I do that? I can't...
>>
>
> I believe this has been asked and answered, namely there needs to be
> further information on how you want to determine the account ids to be
> selected.
>
>
The account_ids can be random.. does not need to have an order, as they all
will be copied some day.

There are more than 1.000.000 million rows in that backup table (
attachments: as pictures, pdfs, etc ), and that's why I can't specify the
account_Id manually.. and also need a limit, so the server won't stop while
performing the COPY

- Also, each file must have the account_id's name. Example for the
> account_id = 124134
> 124134.sql

Please, if you guys could give a help here..
Cheers
P.