09-01-2017 05:53 AM
Hello,
When trying to write a WorkflowTrigger with a plpgsql block statement, the following thing happens:
If not using dollar quotes, an error is thrown at the first line (usually the declaration): ERROR: syntax error at or near "INSERT".
The statement that is sent to the database is the following:
DECLARE _variable INTEGER ; BEGIN _variable := nextval('table_seq') ; INSERT INTO TEST_TABLE (FIELD_1,FIELD_2,FIELD_3) VALUES(_variable ,(('NAMETEST')) ,(('DESCRIPTIONTEST'))); END;
If using dollar quotes (as I should be doing as I try to execute a plpgsql block), 'ERROR: 42703: column "form_name" does not exist' shows up.
That is because the following statement is sent:
DO $$ DECLARE _variable INTEGER ; BEGIN _variable := nextval('table_seq') ; INSERT INTO TEST_TABLE (FIELD_1,FIELD_2,FIELD_3) VALUES(_variable ,@Form_NAME ,@Form_DESCRIPTION); END $$;
The {FORM.NAME} variables are not mapped accordingly.
In conclusion:
I am using Postgresql 9.6 with Postgis 2.3.2 on Ubuntu Server 16.04 Xenial.
The version of GMSC is 16 EP 2
Does anyone have any idea on how I should write a WorkflowTrigger with a plplgsql block on a postgres workflow?
Regards,
Radu
Solved! Go to Solution.
09-01-2017 06:52 AM
Hi Radu,
I think the most simple way is to implement a custom trigger. Here is an old blog post, but it should be working in the same way.
HTH,
Stefano
09-01-2017 07:27 AM
Well I don't think this is the case.
In Oracle and SQL Server there is no problem to put T-SQL blocks or PLSQL blocks inside a workflow trigger. They execute succesfully.
I expect the same thing to happen in Postgressql.
Regards,
Radu
09-01-2017 07:54 AM
unfortunately it is the case because the system is not aware you are defining a PLSQL and, in the case of Postgres, using $ is a way to define string constant.
https://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html
so I guess the only way is to create a custom trigger.
Stefano
09-11-2017 11:55 PM
Thanks everyone.
The custom trigger will not be an optimal solution for us, as we have hundreds of triggers.
Instead, for pgplsql we will use database functions with parameters that we will execute in the standard SqlTrigger (executing a function will not require dollar quotes and it works).
This will give us flexibility in adjusting parameters without having to recompile a plugin each time we have to change something.
Still, it would have been nice to have the same behaivour in all three of he supported databases (now executing blocks inside Oracle and MSSQL works fine, while in postgresql it does not and the block has to be set inside a database function).
Regards,
Radu