CarlosRuiz: Document

2025-09-15T16:03:47Z

Document

New page

In iDempiere is possible to run a process based on a database pl/pgsql postgres function, but the process needs to do certain things to run correctly.

1. The database function receives only one parameter with the AD_PInstance_ID, meaning the instance of the process being executed.

You can derive from AD_PInstance_ID all the information required like AD_Client_ID, CreatedBy (for the user running the process), Record_ID, etc.

You can also get the parameters from AD_PInstance_Para

2. The return of the database function is ignored, so is OK to return simply void.

3. You can inform progress or results inserting into AD_PInstance_Log

4. You MUST update AD_PInstance to report success or error and a message:
* Result=1 -> Success and the ErrorMsg is shown to the user
* Result!=1 -> Failure and the ErrorMsg is shown to the user

5. In the dictionary you just need to register the name of the database function in the field AD_Process.ProcedureName

6. Be careful about using this approach: As you are executing pure direct SQL here, there is no usage of the PO class, so you don't have any of the advantages of using the iDempiere java model, like:
* no validations (range, foreign keys, cross tenants, etc)
* no change log
* no automatic translations
* no automatic trees
* no automatic delete of children
* etc

This is an example that you can use as template:

<syntaxhighlight lang=sql>
-- DROP FUNCTION procedure_example();

CREATE OR REPLACE FUNCTION procedure_example(p_instanceid numeric)
RETURNS void
LANGUAGE plpgsql
AS $BODY$
DECLARE
p_record_id int;
l_charge_name varchar(100);
l_cnt integer := 0;
BEGIN
SELECT i.record_id
INTO p_record_id
FROM ad_pinstance i
WHERE i.ad_pinstance_id=p_instanceid;

SELECT Name
INTO l_charge_name
FROM C_Charge
WHERE C_Charge_ID = p_record_id;

l_cnt := l_cnt + 1;
INSERT INTO AD_PInstance_Log (AD_PInstance_ID, Log_ID, P_Msg, AD_PInstance_Log_UU)
VALUES(p_instanceid, 0, 'To record information or progress you can insert into AD_PInstance_Log: ' || l_cnt, generate_uuid());

/* Register exit message and success updating AD_PInstance with Result=1, use Result!=1 for errors */
UPDATE AD_PInstance SET Result=1, ErrorMsg='Everything was OK' WHERE AD_PInstance_ID=p_instanceid;

END;
$BODY$
;
</syntaxhighlight>

[[Category:HowTo-Technical]]

HowTo: Run Process from Database Function - Revision history

CarlosRuiz: Document