Sharon,
having thought about this some more I think you will need to use an
untrustred procedural language to let you interact with the file system.
I think you would have the option of perlu, tclu or pythonu (although I
haven't used them) as I don't think there's an untrusted version of
plpgsql??.
http://www.postgresql.org/idocs/index.php?xplang.html
Sorry for the earlier red herring
sb
ps. pls post when you get this working.
If I get more time I'll mess about with it over the weekend. I suppose you
could write a generic function such as:
export_sql_to_file ( [pSQLStatement] , [pFileName] , [Format] ,
[Delimiter] )
(
--Drop temp table if exists
--Create temp table from pSQLStatement
--Export temp table data to Filename in Format using Delimiter
--Drop temp table
)
----- Original Message -----
From: "Steve Boyle (Roselink)" <boylesa@roselink.co.uk>
To: "Sharon Cowling" <sharon.cowling@sslnz.com>;
<pgsql-general@postgresql.org>
Sent: Thursday, January 17, 2002 10:19 PM
Subject: Re: [GENERAL] Function problem
> Sharon,
>
> you can set the session user using set session authorisation.
>
> see:
>
http://developer.postgresql.org/docs/postgres/sql-set-session-authorization.
> html and
> http://developer.postgresql.org/docs/postgres/sql-copy.html
>
> regards
>
> steve boyle
>
> P.S. did you mean to include the \ in the function below?
>
> ----- Original Message -----
> From: "Sharon Cowling" <sharon.cowling@sslnz.com>
> To: <pgsql-general@postgresql.org>
> Sent: Thursday, January 17, 2002 9:42 PM
> Subject: [GENERAL] Function problem
>
>
> > I have a problem with a function containing the copy command.
> >
> > The command below does exactly what I need it to do:
> > \COPY person2 TO '/usr/local/pgsql/report.csv' USING DELIMITERS ','
> >
> > However I need this in a function to call from the front-end code, it
> creates fine but errors when I call it:
> >
> >
> > user=# CREATE FUNCTION getreport10() returns int as '
> > user'# BEGIN
> > user'# \COPY person2 TO ''/usr/local/pgsql/report.csv'' USING DELIMITERS
> '','';
> > user'# return 1;
> > user'# END;'
> > user-# LANGUAGE 'plpgsql';
> > CREATE
> >
> > user=# select getreport10();
> > ERROR: You must have Postgres superuser privilege to do a COPY directly
> to or from a file. Anyone can COPY to stdout or from stdin. Psql's \copy
> command also works for anyone.
> >
> > It does work if I give the user superuser privileges but I don't really
> want to do this!
> > Any suggestions?
> >
> > Regards,
> >
> > Sharon Cowling
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
