I want to set the PostGIS namespace dynamically, derived through a RegExp from the SourceDataset_GENERIC parameter, however all my imports end in the 'public' namespace.
Best Answer
Setting DB namespace dynamically from SourceDataset_GENERIC through RegexReplacement
+6- Contributor
Best answer by takashi
In my quick test, as well as the workaround for SQL Server, this dynamic schema setting worked as expected. Assuming that the destination schema name is given as a user parameter called $(SCHEMA).
$(SCHEMA).@Value(fme_feature_type)
Naturally the database should have the schema beforehand. If the schema could be missing when you run the workspace, you could create it with this SQL statement set to the "SQL To Run before Write" parameter in the writer.
create schema if not exists $(SCHEMA)This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+10I have looked at your workspace and you need to remove the double quotes from the last to parts of the @ReplaceRegEx statement, making it look like this:
@ReplaceRegEx($(SourceDataset_GENERIC),^.*[\\/](rgu|gsm)[\\/].*$,\1)- Contributor
I have looked at your workspace and you need to remove the double quotes from the last to parts of the @ReplaceRegEx statement, making it look like this:
@ReplaceRegEx($(SourceDataset_GENERIC),^.*[\\/](rgu|gsm)[\\/].*$,\1)
Unfortunately it still doesn't work:
My input is '/tmp/gsm/t/stadtplan_bildung.gpkg' , so the regex should extract 'gsm' and set this as the namespace for the new table.
But the table ist still created in public.
16612-vector-import-worker.fmw
16612-vector-import-workerlog.txt
+6- Contributor
Further exploration shows that the Regex is now correct, but in dynamic mode FME 2018 seems to still carry this old bug:
https://knowledge.safe.com/questions/3478/postgis-schema.html
Could someone please verify this?
+10Looking at the workspace, there are two suggestions I could make:
1. Does it help if you create an attribute first containing the namespace? And
2. How about creating a second writer and set the first Writer to 'rgu' as a namespace and the second to 'gsm' as a namespace. You could use a TestFilter to define which Writer should be used. It makes the process a little less dynamic, but allows to hardcode the namespace which could be a workaround for now.
+6- Contributor
Thanks for the suggestions, @lars_de_vries
Unfortunately I am still struggling using the dynamic writer to PostGIS and setting any, even static namespace.
Whatever I try, the tables end up in 'public', which is where they do not have to go.
vector-import-worker.fmw is what I have now with an AttributeCreator setting the namespace to the string 'rgu', but it does not get honored.
I tried to create a fully qualified tablename like 'rgu.stadtplan_bildung', but I did not succeed in concatenating a string with the _fme_feature_type variable.
+10You could try this workspace, it is what I ment with my second suggestion.
+6- Contributor
Thanks again, @lars_de_vries
Logic would say that this works, albeit the table fell again into 'public'.
Here is the log: 16617-16613-16612-vector-import-worker-2log.txt
+10Thanks again, @lars_de_vries
Logic would say that this works, albeit the table fell again into 'public'.
Here is the log: 16617-16613-16612-vector-import-worker-2log.txt
Maybe @FMELizard can tell whether this still is a unsolved bug as you suggested earlier, or something else is going on.
Hi @dollargis, I'm not sure if it is common to PostgreSQL/PostGIS writer, there was a similar issue before: "Dynamic" output to SQL Server - no Table Qualifier/database schema ?
+6- Contributor
The problem lies in the combination of dynamic schema creation and any other namespace than 'public'. It seems impossible to get both
- Best Answer
In my quick test, as well as the workaround for SQL Server, this dynamic schema setting worked as expected. Assuming that the destination schema name is given as a user parameter called $(SCHEMA).
$(SCHEMA).@Value(fme_feature_type)
Naturally the database should have the schema beforehand. If the schema could be missing when you run the workspace, you could create it with this SQL statement set to the "SQL To Run before Write" parameter in the writer.
create schema if not exists $(SCHEMA)- Contributor
In my quick test, as well as the workaround for SQL Server, this dynamic schema setting worked as expected. Assuming that the destination schema name is given as a user parameter called $(SCHEMA).
$(SCHEMA).@Value(fme_feature_type)
Naturally the database should have the schema beforehand. If the schema could be missing when you run the workspace, you could create it with this SQL statement set to the "SQL To Run before Write" parameter in the writer.
create schema if not exists $(SCHEMA)Now I am just scratching my head why I couldn't find this in the docs....