Search Postgresql Archives

Re: Automate copy - Postgres 9.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





2016-06-09 12:13 GMT+12:00 John R Pierce <pierce@xxxxxxxxxxxx>:
On 6/8/2016 4:24 PM, Patrick B wrote:

I need to do a file backup for each account_id.

Example:

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

COPY generates CSV and similar formats, not .sql.   only pg_dump, the command line utility, outputs .SQL

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

try...


begin
      FOR crtRow in
            select account_id from backup_table WHERE migrated = 1 AND account_id in $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/' || crtRow.account_id || '.csv';
    end loop;
    return integer;
end


but you can't exactly return 'integer' if its a list of values, so I'm not sure what it is you want to return from this function...



-- 
john r pierce, recycling bits in santa cruz


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';

ERROR:

ERROR:  syntax error at or near "||"
LINE 12:     TO '/var/lib/pgsql/' || crtrow.account_id |...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux