Skip site navigation (1) Skip section navigation (2)

Re: generating dynamic queries using pl/pgsql

From: sarlav kumar <sarlavk(at)yahoo(dot)com>
To: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
Cc: pgsqlnovice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: generating dynamic queries using pl/pgsql
Date: 2005-01-21 16:00:17
Message-ID: 20050121160017.54378.qmail@web51306.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Sean,
 
Thanks, for the help. I got the dynamic query generation part to work.
The only thing left to do is to get the dump of the temporary table.
 
When I try to use COPY inside the pl/pgsql function, I get the following error:
 
 COPY temp1 to ''aff.txt'';
 
WARNING:  Error occurred while executing PL/pgSQL function try2
WARNING:  line 38 at SQL statement
ERROR:  Relative path not allowed for server side COPY command
 
Then I dropped the function, and recreated the function with the following command:
 
COPY temp1 to ''/home/developers/ss2/aff.txt'';

WARNING:  Error occurred while executing PL/pgSQL function try2
WARNING:  line 38 at SQL statement
ERROR:  COPY command, running in backend with effective uid 501, could not open file '/home/developers/ssivakumar/aff.txt' for writing.  Errno = No such file or directory (2).

How can I get the copy command to work from within the pl/pgsql?
I even tried using \copy, it still gives me the above error. 
 
Is there anyway to use \! pg_dump command inside pl/pgsql? I tried it, but it does not allow the character "!". Is there a way to escape "!"?
 
Thanks in advance,
Saranya
 


>http://www.postgresql.org/docs/7.4/interactive/sql-copy.html

>You can use COPY to do this.

>HTH,
>Sean



		
---------------------------------
Do you Yahoo!?
 Yahoo! Search presents - Jib Jab's 'Second Term'

In response to

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2005-01-21 16:11:43
Subject: Re: generating dynamic queries using pl/pgsql
Previous:From: John DeSoiDate: 2005-01-21 15:53:11
Subject: Re: grant all the database object automatically

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group