Re: generating dynamic queries using pl/pgsql

On Jan 21, 2005, at 11:00 AM, sarlav kumar wrote:

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

The tricky part about COPY is that it is executed by the SERVER!
Therefore, the tables can only be written to somewhere writable by the
user running the server process. If, for example, you have a user
named postgres, you could set up a directory that is owned by postgres
and use that for the dumps. /tmp is another place. Of course, all
this has to be done on the SERVER machine; it can't be done locally to
a file. I imagine that is the issue, but others can correct me if I am
wrong on this.

Another option is to COPY to STDOUT and then capture the output.

Sean