Re: Automate copy - Postgres 9.2

On Tue, 2016-06-14 at 13:33 +1200, Patrick B wrote:
>
>
> 2016-06-14 9:47 GMT+12:00 Adrian Klaver <adrian.klaver@aklaver.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.


1) COPY TO produces a text file and having a file created with a suffix
of SQL is slightly misleading. You can specify delimiter and
encapsulation characters. So, a suffix of csv is more appropriate.

2) Assuming backup_table is static and nobody is inserting or updating
data, you just need to read the rows and write out the million or so
individual files. If it's not static, you'll have to update (or delete)
the rows written to file, perhaps SET status = 2, so that you don't
write the same file multiple times.

3) If you are worried about throttling, put a pause into the program
looping through backup_table. You have to write a program to read
backup_table in order to supply your function with an account_id.