Re: [StrongLoop] Problem inserting json into a view in with postgresql connector

[Ryan Graham]

Cross-posting to loopbackjs list.

---------- Forwarded message ----------
From: Stephen Knox <stephe...@gmail.com>
Date: Wed, Mar 25, 2015 at 7:56 AM
Subject: [StrongLoop] Problem inserting json into a view in with postgresql connector
To: stron...@googlegroups.com

I am adding and editing geometry in loopback with PostGIS, and I have the following working fine: 

ProjectGeoms.upsert({"id":"129","projectGeom":{"type":"Polygon","coordinates":[[arrays of x/y co-ordinates]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}}, successCallback, errorCallback)

This is for a geometry that already exists. However, when I add it for a geometry which doesn't exist (the same JSON structure, but with an id 124), then it does not work.

The difference in the SQL statements is below:

  loopback:connector:postgresql SQL: UPDATE "public"."project_geom" SET "id"=$1,"project_geom"=$2 (works)

  loopback:connector:postgresql SQL: INSERT INTO "public"."project_geom" ("id","project_geom") SELECT $1,$2 RETURNING .... (doesn't work)

This is updating a view, but I'm pretty sure the problem is in inserting it into the view, not the trigger, as I get {"error":{"name":"error","status":500,"message":"syntax error at end of input"...

The geometry column type is json in the view and in loopback. The only thing I can think of is that the insert statement requires a cast to json, whereas the update doesn't because the output is already in that format. Or something to do with the SELECT statement in the INSERT INTO statement. Any ideas how to solve this?

Thanks

Stephen

[twilk...@strongloop.com]

Hi Ryan, I am trying to duplicate your error. Can you please tell me a little bit about your application?

1) You mentioned a trigger. Can you please tell me a little about it? Does it only run on INSERT, or does it run on UPDATE as well? Besides the error message, is there any other proof the trigger is not the issue (e.g., do you get the same results with the trigger disabled?)

2) What is your indication that it INSERT doesn’t work? Can you post the exact error message?

3) This is updating a VIEW. Can you please tell me about the underlying tables of the view?

4) Have you been able to INSERT the same data into the view by hand?

Thanks,

- Thomas;

[Stephen Knox]

Hi Thomas,

I am Stephen, who asked the original question.

The trigger is required to make a view writable in Postgres so I can't test without it - it runs on insert, update and delete. 

This is the log I get:

  loopback:connector:postgresql SQL: INSERT INTO "public"."project_geom" ("ref","project_geom") SELECT $1,$2 RETURNING 

Parameters: 124,{"type":"Polygon","coordinates":[[[-0.1028144359588623,51.37445934986937],[-0.10126948356628418,51.37301272340493],-0.09850144386291503,51.37462008332153],[-0.10086178779602051,51.37537016530327],[-0.1028144359588623,51.37445934986937]]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}} +2ms

The full error message is below:

  loopback:connector:postgresql error: syntax error at end of input

    at Connection.parseE (C:\Bitnami\wappstack-5.4.30-0\apache2\htdocs\project\api\node_modules\loopback-connector-postgresql\node_modules\pg\lib\connection.js:534:11)

    at Connection.parseMessage (C:\Bitnami\wappstack-5.4.30-0\apache2\htdocs\project\api\node_modules\loopback-connector-postgresql\node_modules\pg\lib\connection.js:361:17)

    at Socket.<anonymous> (C:\Bitnami\wappstack-5.4.30-0\apache2\htdocs\project\api\node_modules\loopback-connector-postgresql\node_modules\pg\lib\connection.js:105:22)

    at Socket.emit (events.js:95:17)

    at Socket.<anonymous> (_stream_readable.js:765:14)

    at Socket.emit (events.js:92:17)

    at emitReadable_ (_stream_readable.js:427:10)

    at emitReadable (_stream_readable.js:423:5)

    at readableAddChunk (_stream_readable.js:166:9)

    at Socket.Readable.push (_stream_readable.js:128:10) +15ms

If I run the following query: INSERT INTO project_geom (ref,project_geom) VALUES (124,'{"type":"Polygon","coordinates":[[[-0.1028144359588623,51.37445934986937],[-0.10126948356628418,51.37301272340493],[-0.09850144386291503,51.37462008332153],[-0.10086178779602051,51.37537016530327],[-0.1028144359588623,51.37445934986937]]],"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}'::json);

I am able to insert the value without issue.The view schema is (ref:integer, project_geom::json)

Is this of any help? I guess the problem is in the type handling somewhere.

Stephen

[Stephen Knox]

I solved this - problem was I didn't have an id field in my model - see further discussion here: http://stackoverflow.com/questions/29746017/inserting-geometry-json-into-loopbackjs-model 

Thanks

Stephen