On Mon, Jun 13, 2016 at 10:07 PM, rob stone <floripa...@gmail.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.kla...@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. > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.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.
