Re: Export data from a query ran by pgagent

Hi Dave,

Thanks for your quick response,

I'm afraid dumping the query results into another table is not possible as
the queried database is a read-only copy of our main database.

I assume there's no way to schedule remote shell script executions with
pgAgent right? I didn't find anything on the documentation about that
specific point.

Best,

Gonzalo Rubio-Casas

El jue, 26 nov 2020 a las 15:55, Dave Page (<dpage(at)pgadmin(dot)org>) escribió:

> 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
>
>