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

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