Обсуждение: Automate copy - Postgres 9.2

Поиск

Список

Период

Сортировка

Automate copy - Postgres 9.2

От

Patrick B

Дата:

09 июня 2016 г., 02:25:41

Hi guys,

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

Can I create a PLPGSQL function to perform that?

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

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

Can anybody give me a help here please?

Re: Automate copy - Postgres 9.2

От

John R Pierce

Дата:

09 июня 2016 г., 03:13:43

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

Re: Automate copy - Postgres 9.2

От

Adrian Klaver

Дата:

09 июня 2016 г., 03:19:18

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

Re: Automate copy - Postgres 9.2

От

Patrick B

Дата:

09 июня 2016 г., 03:47:14

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.

Re: Automate copy - Postgres 9.2

От

Patrick B

Дата:

09 июня 2016 г., 03:50:47

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 "||"
LINE 12: TO '/var/lib/pgsql/' || crtrow.account_id |...

Re: Automate copy - Postgres 9.2

От

John R Pierce

Дата:

09 июня 2016 г., 04:15:16

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

Re: Automate copy - Postgres 9.2

От

Patrick B

Дата:

09 июня 2016 г., 04:47:57

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

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

Re: Automate copy - Postgres 9.2

От

John R Pierce

Дата:

09 июня 2016 г., 04:58:44

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

Re: Automate copy - Postgres 9.2

От

Patrick B

Дата:

09 июня 2016 г., 05:04:57

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

Re: Automate copy - Postgres 9.2

От

Rob Sargent

Дата:

09 июня 2016 г., 06:05:28

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

Re: Automate copy - Postgres 9.2

От

"David G. Johnston"

Дата:

09 июня 2016 г., 06:51:00

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.

Re: Automate copy - Postgres 9.2

От

John R Pierce

Дата:

09 июня 2016 г., 07:50:32

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

Re: Automate copy - Postgres 9.2

От

rob stone

Дата:

09 июня 2016 г., 22:15:40

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

Re: Automate copy - Postgres 9.2

От

Patrick B

Дата:

14 июня 2016 г., 00:42:56

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

Re: Automate copy - Postgres 9.2

От

Adrian Klaver

Дата:

14 июня 2016 г., 00:47:50

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

Re: Automate copy - Postgres 9.2

От

Patrick B

Дата:

14 июня 2016 г., 04:33:52

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

Re: Automate copy - Postgres 9.2

От

rob stone

Дата:

14 июня 2016 г., 05:07:32

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.

Re: Automate copy - Postgres 9.2

От

Melvin Davidson

Дата:

14 июня 2016 г., 05:25:34

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.

Re: Automate copy - Postgres 9.2

От

Patrick B

Дата:

14 июня 2016 г., 06:48:30

Yep... thanks Melvin..

It's working as expected now

Cheers

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Automate copy - Postgres 9.2