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.
Solved
Setting DB namespace dynamically from SourceDataset_GENERIC through RegexReplacement
+1- 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)
+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
lars_de_vries wrote:
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
+1- 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.
+1- 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.
+1- 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
+10dollargis wrote:
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
Maybe @FMELizard can tell whether this still is a unsolved bug as you suggested earlier, or something else is going on.
+19- Contributor
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 ?
+1- Contributor
The problem lies in the combination of dynamic schema creation and any other namespace than 'public'. It seems impossible to get both
+19- Contributor
- 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
takashi wrote:
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....
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.