Re: Export data from a query ran by pgagent

Hi

On Thu, Nov 26, 2020 at 2:42 PM Gonzalo Rubio Casas <
gonzalo(dot)rubio(dot)casas(at)gmail(dot)com> wrote:

> Hi everyone,
>
> I hope someone has an idea because I have been stuck with this for a
> couple of days. I'm using pgagent to allow some users of my database to
> schedule period *remote* queries that create some reports.
>
> However, I do not find anywhere on the manual or the mailing list how to
> retrieve the result of the ran queries. Alternatively, I have used COPY
> statement although that requires the SQL query being run by a superuser
> which is not an option anymore as this compromises the general security of
> the system.
>
> Does anyone know or has come with a hack for exporting the information
> retrieved by a query run by pgagent? It is important to note that pgagent
> jobs are connected to a remote database so the possibility of a shell
> script is not possible I'm afraid.
>

pgAgent isn't designed to do this sort of thing I'm afraid. Given that you
can't use a shell script (or COPY), probably the best you could do is have
the query results be dumped into a table from which the users can query the
data when they need it.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com