Re: Automate copy - Postgres 9.2

[Rob Sargent <robjsargent@xxxxxxxxx>]

On Jun 8, 2016, at 8:04 PM, Patrick B <patrickbakerbr@xxxxxxxxx> wrote:

2016-06-09 13:58 GMT+12:00 John R Pierce <pierce@xxxxxxxxxxxx>:

On 6/8/2016 6:47 PM, Patrick B wrote:

21 is the number of IDS that I wanna perform that COPY command....

that didn't answer my question.  if you call your function like SELECT myfunction(21);   as you showed, where are those 21 ID's coming from?

I don't know what u need, mate:

CREATE or REPLACE FUNCTION function(integer)

RETURNS void AS $$

declare

   crtRow record;

begin

      FOR crtRow in EXECUTE 'select DISTINCT(account_id) from backup_table WHERE migrated = 1 AND account_id IN '|| $1

      LOOP

    COPY

        (SELECT * FROM backup_table WHERE migrated = 1 AND account_id = crtRow.account_id)

    TO '/var/lib/pgsql/' || crtrow.account_id || '.csv';

    end loop;

end

$$ language 'plpgsql';

Isn't this what u wanna know?
select DISTINCT(account_id) from backup_table WHERE migrated = 1

So you want to call your function in a loop, or drop the passed in id and just do all the account at once, take your pick.