Thread: Re: Running a stored procedure via pgagent, need an examp le

> ------- Original Message -------
> From: novnov <novnovice@gmail.com>
> To: pgsql-general@postgresql.org
> Sent: 13/08/07, 17:36:12
> Subject: [GENERAL] Running a stored procedure via pgagent, need an example
>
> Can someone give me a simple example of the way in which I might be able to
> call a stored procedure or query using pgagent? I have never scripted a
> postgres .sql etc to run via cron or pgagent and an example of the syntax
> would be helpful. In pgagent terms, the Step is set up as SQL, and it's the
> definition that I would like an example of. I am guessing that one would
> save the routine off to a .sql file, then call via \i?

Just enter the SQL to execute in the textbox on the step dialog, e.g.

SELECT my_proc();

Regards, Dave

Thank you, just had time to test this.

I wrote a simple sproc that adds a row to a test table. I've created a one
step job

db = db the sproc is in
kind = sql
def = SELECT proc_test01_insertrow();

set to run on each minute of every hour every day, starting today 00:00:01

The job, step, and schedule all show as enabled. But it never runs. I've
even restarted postgres...what else could be involved?


Dave Page-3 wrote:
>
>
>
>> ------- Original Message -------
>> From: novnov <novnovice@gmail.com>
>> To: pgsql-general@postgresql.org
>> Sent: 13/08/07, 17:36:12
>> Subject: [GENERAL] Running a stored procedure via pgagent, need an
>> example
>>
>> Can someone give me a simple example of the way in which I might be able
>> to
>> call a stored procedure or query using pgagent? I have never scripted a
>> postgres .sql etc to run via cron or pgagent and an example of the syntax
>> would be helpful. In pgagent terms, the Step is set up as SQL, and it's
>> the
>> definition that I would like an example of. I am guessing that one would
>> save the routine off to a .sql file, then call via \i?
>
> Just enter the SQL to execute in the textbox on the step dialog, e.g.
>
> SELECT my_proc();
>
> Regards, Dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>

--
View this message in context:
http://www.nabble.com/Re%3A-Running-a-stored-procedure-via-pgagent%2C-need-an-examp-le-tf4263270.html#a12185196
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

novnov wrote:
> Thank you, just had time to test this.
>
> I wrote a simple sproc that adds a row to a test table. I've created a one
> step job
>
> db = db the sproc is in
> kind = sql
> def = SELECT proc_test01_insertrow();
>
> set to run on each minute of every hour every day, starting today 00:00:01
>
> The job, step, and schedule all show as enabled. But it never runs. I've
> even restarted postgres...what else could be involved?

The normal problems we see are:

- pgAgent is connecting to a database other than the one containing the
pgagent schema.

- The user account that pgAgent runs under doesn't have trust access to
the database, or a suitable pgpass file to give it the password.

You can also enable debugging on the pgAgent command line which should
give more info about whats going on.

Regards, Dave