@rbovard​ I couldn't reproduce your error, I've attached an example workspace that creates a table, creates a view and then writes to the view. So I suspect permissions or possibly an incorrect view name if you're using a table qualifier.

 Requirements for updateable views.

Thanks @Mark Stoakes​ for your answer and your example.

I'm glad to see FME can write into views, but it still not working on my side.

It's strange because:

• If I rename my feature type in writer, it creates a new table in my database --> Not a rights issue
• If I set the writer action as `Create if needed` it shows me `ERROR: relation "my_view" already exists` --> Not a qualifier issue
• If I set the writer `Use existing` (which I was initially doing), it shows me `Table 'my_view' does not exist.`

Maybe it's something with PostGIS writer? Like I said, I can update my view via other client (QGIS), it also work in pure SQL:

INSERT INTO <schema>.<view>(<geom>)
VALUES (ST_GeomFromText('...'));

Any other idea?

Best regards,

Rémi

INSERT INTO <schema>.<view>(<geom>)
VALUES (ST_GeomFromText('...'));

@rbovard​ My first example was PostgreSQL so I thought it might be PostGIS that was the issue here. So I created a second example with a PostGIS view (attached). This seems to work OK as well. So I suspect it's the way that your view has been created that is causing FME to stumble.  

I think you'll need to share you example (SQL, workspace & sample data)  with the community, or open a support issue with your local FME Reseller. (Please post the resolution answer back here if there is one)

INSERT INTO <schema>.<view>(<geom>)
VALUES (ST_GeomFromText('...'));

Thanks a lot @Mark Stoakes​ for this second example.

In fact you're still using a PostgreSQL writer and features with lat/lng attributes, but - if I understand it right - they aren't spatial features and you're not writing in a spatial view.

My use case is writing spatial features, in a spatial view via a PostGIS writer.

Is it clearer for you or should I try to give you a basic workspace to illustrate that?

Anyway, thanks again for trying to solve my issue ;)

Best regards,

Rémi

INSERT INTO <schema>.<view>(<geom>)
VALUES (ST_GeomFromText('...'));

@rbovard​ Sorry, that was my error. I forgot to switch the last, and important, writer to PostGIS. When I do that I reproduce your error. My guess is that there is additional SQL that needs to be called using the GDAL driver to support writing to an editable view (or perhaps when you create the view). 

At the moment it looks like FME doesn't support writing to an editable view that has a spatial column.

Revised workspace that reproduces @rbovard​ 's error when writing to a spatial editable view.

Hi @Mark Stoakes​ 

Do you have any update on this issue?

Thanks in advance for your feedback.

Rémi

@rbovard​ this hasn't changed. I believe that it is the way the view is created. In the attached workspace, , as far as I can tell, the view created is not recognised in QGIS. It returns the error;

Layer is not valid: The layer dbname='ORYX' host=localhost port=5432 sslmode=disable key='"longitude"' srid=4326 type=Point table="public"."cellsignalsGEOM" (geom) is not a valid layer and can not be added to the map. Reason: 

If you know how to modify the example in the workspace to create a valid view that QGIS can read then I can try and determine why FME can't read it.

Thanks a lot for your feedback.

I'm not sure it's related to the view creation, since here I can load my view in QGIS but not write in it via FME (it works from QGIS or even pure SQL).

I'll try to create a very basic workspace to demonstrate it.

@Mark Stoakes​ Please find here an attached workspace wich creates a table, then a view and try to write in it.

For the record, the view is editable from QGIS or pure SQL.

@Mark Stoakes​ Is it better with my script? Are you able to load this view into QGIS and edit it?

@rbovard​ did you find a solution to this problem? That's exactly what I'm facing right now and i cannot find a solution.

Hi @takacstamas​ 

Unfortunately not.

The workaround I used was to write to a temp table with the same structure as my editable view, and then using a SQL Executor, insert the data with a query of type

INSERT INTO <my_view> SELECT * FROM <my_temp_table>

And then drop my temp table also with SQL.

It wasn't the most straightforward, but it worked. After that, I didn't try with the latest versions of FME, so maybe there have been improvements in that area.

Rémi

Hi @rbovard and @takacstamas,

I’m happy to share that an update to FME Form 2024.1 has added the ability to write to a view with a spatial column for PostGIS/PostgreSQL formats thanks to a problem report Mark had filed for the team identified internally as (FMEENGINE-70047).

So far the functionality is just available in FME 2024.1 betas. You can access the beta install here: https://engage.safe.com/support/downloads/#beta

If you have a chance to give it a try, please let us know if you have any feedback on it!

Happy FME’ing!

Jovita

Hi @jovitaatsafe!

I’m installed the latest version of FME Form 2024.1 Beta (Build 24553) and it work perfectly!
Thank you for resolving this issue, the FME team is the Best!

Tamás