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

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