Re: Automate copy - Postgres 9.2

On 06/08/2016 04:24 PM, Patrick B wrote:

Hi guys,

I need to do a file backup for each account_id.

Example:

|COPY (SELECT*FROMbackup_table WHEREid =1112ANDstatus
=1)TO'/var/lib/pgsql/1112.sql';COPY (SELECT*FROMbackup_table WHEREid
=1113ANDstatus =1)TO'/var/lib/pgsql/1113.sql';COPY
(SELECT*FROMbackup_table WHEREid =1114ANDstatus
=1)TO'/var/lib/pgsql/1114.sql';|


Can I create a PLPGSQL function to perform that?


I tried but isn't working... don't know how to determinate that:

Define not working.

FYI, COPY:

https://www.postgresql.org/docs/9.2/static/sql-copy.html

Notes

"Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client. COPY naming a file is only allowed to database superusers, since it allows reading or writing any file that the server has privileges to access."

        CREATE or REPLACE FUNCTION dump(integer)

        RETURNS integer AS $$


        declare

           crtRow record;

        begin

              FOR crtRow in execute 'select account_id from backup_table
        WHERE migrated = 1 AND account_id = '|| $1


              LOOP

            COPY

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

            TO '/var/lib/pgsql/gorfs_backup/%s.sql';

            end loop;

            return integer;

        end


        $$ language 'plpgsql';

- Each account_Id would have a file with its ID
- When calling the function, I want to specify the numbers of
account_Ids I wanna do the dump

Unclear.

Single id as you show, a range of numbers or an array of numbers?

Can anybody give me a help here please?

You will get better help quicker if you are clearer in your problem description and include illustrative examples of what you want to achieve.

COPY 
(
SELECT * FROM backup_table WHERE id = 1112 AND status = 1
) 
TO '/var/lib/pgsql/1112.sql';

COPY 
(
SELECT * FROM backup_table WHERE id = 1113 AND status = 1
) 
TO '/var/lib/pgsql/1113.sql';

COPY 
(
SELECT * FROM backup_table WHERE id = 1114 AND status = 1
) 
TO '/var/lib/pgsql/1114.sql';