Re: generating dynamic queries using pl/pgsql

Saranya,

Just my personal suggestion, but I would move on to a scripting
language like perl.  Once you learn a bit of it, doing stuff like what
you are proposing is trivial.  Unfortunately, I don't have more
suggestions on making psql work for you.  If you want to automate the
whole process, then you will benefit from learning a scripting
language.  Others can correct me if they don't share my sentiments.

Sorry,
Sean

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

>
> Hi Sean,
>  
> The problem is that I dont have permission to create directories as a
> postgres user.
> If I can get the \copy command or the \! pg_dump command to work, that
> would be great.
>  
> Thanks,
> Saranya
>
> Sean Davis <sdavis2@mail.nih.gov> wrote:
>
> 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
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com