Re: generating dynamic queries using pl/pgsql

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