Greetings!
I would like to set an attribute of my feature as the value returned by a pl/pgsql function. Currently, I use the SQLExecutor for every feature. It's much slower than using the pl/pgsql function in the INSERT.
The expected result would be that FME produce the following insert statement.
INSERT INTO my_table VALUES (17, 'abc', MY_FUNCTION(17));
I'm using fme build 16716 with postgreql version 9.5.
Kind regards,
Jean-Denis
Best answer by david_r
Thanks for the details. Unfortunately I think your only option here is to retrieve the sequence value using the SQLExecutor and then use that value in both your feature types. There is currently no way of retrieving an id that was assigned by the database other than re-reading the feature.
However, I understand that making a separate call to the database for each feature can be slow, so you could consider pre-fetching several sequence values at a time:
Postgres: https://www.depesz.com/2008/03/20/getting-multiple-values-from-sequences/
You will then have to implement local caching of these sequence blocks and merge them one-by-one to your features before the writer.
Not sure I understand, can you elaborate?
You can use e.g. an AttributeCreator to create an insert statement and use it inside the the SQLExecutor:
If you need the value from MY_FUNCTION in your workspace you can use
select MY_FUNCTION(17) as my_valuein the SQLExecutor and then expose "my_value" in your workspace.
+46If you need the value derived by a function, it is normally much quicker to use a featurewriter to create a temporary table, and then use a single sqlexecutor to run the function against all the data in a single sql statement.
Thank you @david_r for your answer!
I will give you a little more background. I parse a SQL table of many hundred thousands of points.
I create features in a first feature type. The primary key of this feature must be generated by the database using a sequence. This part doesn't involve SQLExecutor.
Then, always using the same parsed points, I create features to another feature type where I need foreign key to the first feature type. It's really quick to find the foreign key to use with a small pl/pgsql function.
The throughput of SQLExecutor is really low (a query on a distant database for every feature). So, I would like to avoid it by calling the function in the INSERT statements instead of calling the SQLExecutor first, then writing the feature with the attribute exposed by the SQLExecutor.
Is it possible to pass a SQL function as attribute value to a writer?
Thanks for the details. Unfortunately I think your only option here is to retrieve the sequence value using the SQLExecutor and then use that value in both your feature types. There is currently no way of retrieving an id that was assigned by the database other than re-reading the feature.
However, I understand that making a separate call to the database for each feature can be slow, so you could consider pre-fetching several sequence values at a time:
Postgres: https://www.depesz.com/2008/03/20/getting-multiple-values-from-sequences/
You will then have to implement local caching of these sequence blocks and merge them one-by-one to your features before the writer.
