Re: Call procedure from a Job, Test a Job in pgAdmin?

The job has to pick up a record with the status 'Waiting', and insert it in
another table (test table by now).

Later, I will need to send data to another database, using db link.

requests_curr_req() code is as follows:

CREATE OR REPLACE FUNCTION requests_curr_req()

RETURNS void AS

$BODY$

DECLARE

process_status varchar (10);

request_id varchar (100);

v_request_id varchar (100);

v_retries integer := 0;

v_suceeded boolean := false;

emp_rec RECORD;

BEGIN

for emp_rec in

(select * from request_queue

where process_status = 'Waiting')

loop

v_suceeded := false;

v_request_id := emp_rec.request_id;

while not v_suceeded or v_retries = 10

loop

begin

insert into requests_test (request_id, form_type,
submit_date, request_email, request_description

)

select request_id,

form_type,

submit_date,

request_email,

request_description

from requests

where request_id = v_request_id;

v_suceeded := true;

exception

when others

then

v_retries := v_retries + 1;

update request_queue

set retry_counter = v_retries

where request_id = v_request_id;

v_suceeded := false;

end;

end loop;

if v_suceeded

then

update request_queue

set process_status = 'Completed',

processed_time = LOCALTIMESTAMP,

retry_counter = v_retries

where request_id = v_request_id;

end if;

end loop;

commit;

RETURN;

END;

$BODY$

LANGUAGE plpgsql

Thank you for your consideration,

Leon

From: David Johnston [mailto:polobo(at)yahoo(dot)com]
Sent: Tuesday, June 21, 2011 10:30 AM
To: 'Leon Match'; pgsql-general(at)postgresql(dot)org
Subject: RE: [GENERAL] Call procedure from a Job, Test a Job in pgAdmin?

What's a Job?

SELECT function() is the simpliest way to way to call a function.

What does "requests_curr_req()" do?

EXECUTE function() will "work" depending upon what function does.

So, what do you mean "nothing worked"? Errors, zero results (but no
failure), what?

You may want to look at section 39.12 in the documentation as well "Porting
from Oracle PL/SQL" (section 39 itself is going to be helpful as well).

Have you considered professional support services that have previously done
Oracle migrations? And no, I am not one of them.

David J.

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Leon Match
Sent: Tuesday, June 21, 2011 10:00 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Call procedure from a Job, Test a Job in pgAdmin?

Hello,

Could you please advise on how to call a Procedure (Function) from a Job?

I have created a Job with one step, and a scheduler.

The Job is suppose to run every minute, and call a procedure (function).

I tried different scenarios in a Step Definition:

requests_curr_req();

SELECT requests_curr_req();

EXECUTE requests_curr_req();

EXECUTE PROCEDURE requests_curr_req();

but nothing worked!?

Could you please advise the right way to do that?

Also, what would be the way to test in pgAdmin, if the Job is running at
all?

Thank you,

Leon Match

leon(dot)match(at)convergia(dot)net