Re: Automate copy - Postgres 9.2

On Mon, Jun 13, 2016 at 10:07 PM, rob stone <floriparob(at)gmail(dot)com> wrote:

> On Tue, 2016-06-14 at 13:33 +1200, Patrick B wrote:
> >
> >
> > 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.
>
>
> 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.
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

It sounds to me like you are over complicating the problem.
You should make the function accept two parameters, a start and a stop id.
eg: CREATE or REPLACE FUNCTION function(start_account_id integer,
end__account_id)

Then you can modify it to fetch through a temp table table of just the
account_id's you need.

eg:
DECLARE
refcur refcursor;

BEGIN
CREATE TEMP TABLE accts_to_copy
AS SELECT account_id from backup_table
WHERE account_id >= start_account_id
AND account_id <= end_account_id;

<some_labe>
FOR account_id IN refcur
SELECT account_id FROM accts_to_copy LOOP
do_your_copy
END LOOP <some_labe>;

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.