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
>
