Automate copy - Postgres 9.2

On 6/8/2016 4:24 PM, Patrick B wrote:

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

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.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2016-06-09 12:19 GMT+12:00 Adrian Klaver <adrian.klaver@aklaver.com>:

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.

Don't know how to determinate that for each id must have a different file.

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?

select function(21);

Where 21 = Number of ids

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.

I already have:

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

That's what I want.. but i don't wanna do that manually...

I need a separate file for each id.

2016-06-09 12:13 GMT+12:00 John R Pierce <pierce@hogranch.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 "||"

On 6/8/2016 5:46 PM, Patrick B wrote:

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

select function(21);

Where 21 = Number of ids

how do you get the specific ID's from "21" ?

--
john r pierce, recycling bits in santa cruz

2016-06-09 13:15 GMT+12:00 John R Pierce <pierce@hogranch.com>:

On 6/8/2016 5:46 PM, Patrick B wrote:

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

select function(21);

Where 21 = Number of ids

how do you get the specific ID's from "21" ?

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

twenty one times this sql:

COPY

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?

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

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

2016-06-09 13:58 GMT+12:00 John R Pierce <pierce@hogranch.com <mailto:pierce@hogranch.com>>:
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.

POn Wednesday, June 8, 2016, Patrick B <patrickbakerbr@gmail.com> wrote:

ERROR: syntax error at or near "||"

LINE 12: TO '/var/lib/pgsql/' || crtrow.account_id |...

Which tells me you cannot use an expression as a file name. The docs
support this conclusion.

You probably need to use EXECUTE after constricting a string.

You could also try psql in a shell script.

David J.

On 6/8/2016 7:04 PM, Patrick B wrote:

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

where .... and account_id in 21;

? I don't think that's what you want.

--
john r pierce, recycling bits in santa cruz

On Wed, 2016-06-08 at 23:50 -0400, David G. Johnston wrote:

POn Wednesday, June 8, 2016, Patrick B <patrickbakerbr@gmail.com>
wrote:

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

Which tells me you cannot use an expression as a file name.  The docs
support this conclusion.

You probably need to use EXECUTE after constricting a string.

You could also try psql in a shell script.

David J. 

If this is something that you'll never ever have to do again in the
future, you could download ExecuteQuery which has a function to export
a result set as either XML or CSV delimited file output.

OTOH, if this is going to be something run on a regular basis, I think
you'd be better off writing a little program, or as David suggested
embedding this into a shell script.

HTH,
Rob

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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...

Cheers
Patrick

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.

Cheers
Patrick

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2016-06-14 9:47 GMT+12:00 Adrian Klaver <adrian.klaver@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.

On Tue, 2016-06-14 at 13:33 +1200, Patrick B wrote:

2016-06-14 9:47 GMT+12:00 Adrian Klaver <adrian.klaver@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

On Mon, Jun 13, 2016 at 10:07 PM, rob stone <floriparob@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.klaver@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.

Yep... thanks Melvin..

It's working as expected now

Cheers