From: | Patrick B <patrickbakerbr(at)gmail(dot)com> |
---|---|
To: | John R Pierce <pierce(at)hogranch(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Automate copy - Postgres 9.2 |
Date: | 2016-06-09 00:50:04 |
Message-ID: | CAJNY3it3JoZyRTjJFPp+BK0sGYgiSFz6WKmRFc-iM8TPAqmpLw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2016-06-09 12:13 GMT+12:00 John R Pierce <pierce(at)hogranch(dot)com>:
> 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 |...
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2016-06-09 01:15:04 | Re: Automate copy - Postgres 9.2 |
Previous Message | Patrick B | 2016-06-09 00:46:28 | Re: Automate copy - Postgres 9.2 |