On Mon, Jun 13, 2016 at 10:07 PM, rob stone <floriparob@xxxxxxxxx> wrote:
1) COPY TO produces a text file and having a file created with a suffixOn Tue, 2016-06-14 at 13:33 +1200, Patrick B wrote:
>
>
> 2016-06-14 9:47 GMT+12:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:
> > 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.
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@xxxxxxxxxxxxxx)
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.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
