Re: [GENERAL] Automate copy - Postgres 9.2

Yep... thanks Melvin.. It's working as expected now Cheers

Re: [GENERAL] Automate copy - Postgres 9.2

On Mon, Jun 13, 2016 at 10:07 PM, rob stone wrote: > On Tue, 2016-06-14 at 13:33 +1200, Patrick B wrote: > > > > > > 2016-06-14 9:47 GMT+12:00 Adrian Klaver : > > > On 06/13/2016 02:42 PM, Patrick B wrote: > > > > Hi guys, > > > > > > > > I

Re: [GENERAL] Automate copy - Postgres 9.2

On Tue, 2016-06-14 at 13:33 +1200, Patrick B wrote: > > > 2016-06-14 9:47 GMT+12:00 Adrian Klaver : > > On 06/13/2016 02:42 PM, Patrick B wrote: > > > Hi guys, > > > > > > I created this function: > > > > > >             CREATE or REPLACE FUNCTION function(account_id

Re: [GENERAL] Automate copy - Postgres 9.2

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

Re: [GENERAL] Automate copy - Postgres 9.2

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 =

Re: [GENERAL] Automate copy - Postgres 9.2

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

Re: [GENERAL] Automate copy - Postgres 9.2

On Wed, 2016-06-08 at 23:50 -0400, David G. Johnston wrote: > > POn Wednesday, June 8, 2016, Patrick B > wrote: > > > ERROR:  syntax error at or near "||" > > > LINE 12:     TO '/var/lib/pgsql/' || crtrow.account_id |... > > > > Which tells me you cannot use an

Re: [GENERAL] Automate copy - Postgres 9.2

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: [GENERAL] Automate copy - Postgres 9.2

POn Wednesday, June 8, 2016, Patrick B 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

Re: [GENERAL] Automate copy - Postgres 9.2

> On Jun 8, 2016, at 8:04 PM, Patrick B wrote: > > > > 2016-06-09 13:58 GMT+12:00 John R Pierce >: > On 6/8/2016 6:47 PM, Patrick B wrote: > > 21 is the number of IDS that I wanna perform that COPY command > >

Re: [GENERAL] Automate copy - Postgres 9.2

2016-06-09 13:58 GMT+12:00 John R Pierce : > 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

Re: [GENERAL] Automate copy - Postgres 9.2

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

Re: [GENERAL] Automate copy - Postgres 9.2

2016-06-09 13:15 GMT+12:00 John R Pierce : > 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

Re: [GENERAL] Automate copy - Postgres 9.2

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: [GENERAL] Automate copy - Postgres 9.2

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

Re: [GENERAL] Automate copy - Postgres 9.2

2016-06-09 12:19 GMT+12:00 Adrian Klaver : > 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

Re: [GENERAL] 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

Re: [GENERAL] 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

[GENERAL] Automate copy - Postgres 9.2

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