Hexagon Geospatial
MENU

GeoMedia Smart Client

GeoMedia Smart Client community discussion board is where you can create, contribute and share information and knowledge in regards to configuring as well as working with GeoMedia Smart Client. Find your answers, share your knowledge and help build a strong GeoMedia Smart Client community.
Showing results for 
Search instead for 
Do you mean 
Reply
Highlighted
Frequent Contributor
Posts: 77
Registered: ‎10-12-2015
Accepted Solution

Creating a workflow trigger with a plpgsql block

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:

  • without using dollar quotes the form variables are mapped accordingly, but the statement is not valid plpgsql code.
  • using dollar quotes the statement is valid plpgsql code, but the varialbes are not mapped accordingly.

 

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

Staff
Posts: 1,036
Registered: ‎10-18-2015

Re: Creating a workflow trigger with a plpgsql block

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

Stefano Turcato
Presale Engineer
Hexagon Geospatial
Frequent Contributor
Posts: 77
Registered: ‎10-12-2015

Re: Creating a workflow trigger with a plpgsql block

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

Staff
Posts: 1,036
Registered: ‎10-18-2015

Re: Creating a workflow trigger with a plpgsql block

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

 

Stefano Turcato
Presale Engineer
Hexagon Geospatial
Frequent Contributor
Posts: 77
Registered: ‎10-12-2015

Re: Creating a workflow trigger with a plpgsql block

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

Do you need immediate support?
If you encounter a critical issue and need immediate assistance please submit a Service Request through our Support Portal.