[GENERAL] Using Insert with case
Hi select case when somevariable = 2 then (insert into pipe (line) select bob.edge_data.edge_id from bob.edge_data, bob.node, pipe where st_intersects(st_startpoint(bob.edge_data.geom), bob.node.geom) and bob.node.node_id = 415 and pipe.id = 1) I am attempting to use the above. However, with or without the enclosing brackets I get a syntax error on the word into. Help will be appreciated. Bob
Re: [GENERAL] Using Insert with case
On Wed, Aug 8, 2012 at 8:26 AM, Bob Pawley wrote: > Hi > > select > case when somevariable = 2 > then (insert into pipe (line) ... > > I am attempting to use the above. However, with or without the enclosing > brackets I get a syntax error on the word into. Utterly untested, but does it work if you put a RETURNING clause onto the INSERT? That would make it functionally similar to a SELECT, a technique that works for me in other situations. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using Insert with case
On 8 Aug 2012, at 24:26, Bob Pawley wrote: > Hi > > select > case when somevariable = 2 > then (insert into pipe (line) > select bob.edge_data.edge_id > from bob.edge_data, bob.node, pipe > where st_intersects(st_startpoint(bob.edge_data.geom), bob.node.geom) > and bob.node.node_id = 415 > and pipe.id = 1) > > I am attempting to use the above. However, with or without the enclosing > brackets I get a syntax error on the word into. Is that somehow different from this? insert into pipe (line) select bob.edge_data.edge_id from bob.edge_data, bob.node, pipe where st_intersects(st_startpoint(bob.edge_data.geom), bob.node.geom) and bob.node.node_id = 415 and pipe.id = 1 and somevariable = 2 Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using Insert with case
Hi Alban Probably no difference except I have four cases and I was trying, in an attempt to save processing time, to compact commands a little. Bob -Original Message- From: Alban Hertroys Sent: Tuesday, August 07, 2012 3:40 PM To: Bob Pawley Cc: Postgresql Subject: Re: [GENERAL] Using Insert with case On 8 Aug 2012, at 24:26, Bob Pawley wrote: Hi select case when somevariable = 2 then (insert into pipe (line) select bob.edge_data.edge_id from bob.edge_data, bob.node, pipe where st_intersects(st_startpoint(bob.edge_data.geom), bob.node.geom) and bob.node.node_id = 415 and pipe.id = 1) I am attempting to use the above. However, with or without the enclosing brackets I get a syntax error on the word into. Is that somehow different from this? insert into pipe (line) select bob.edge_data.edge_id from bob.edge_data, bob.node, pipe where st_intersects(st_startpoint(bob.edge_data.geom), bob.node.geom) and bob.node.node_id = 415 and pipe.id = 1 and somevariable = 2 Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using Insert with case
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bob Pawley Sent: Tuesday, August 07, 2012 6:26 PM To: Postgresql Subject: [GENERAL] Using Insert with case Hi select case when somevariable = 2 then (insert into pipe (line) select bob.edge_data.edge_id from bob.edge_data, bob.node, pipe where st_intersects(st_startpoint(bob.edge_data.geom), bob.node.geom) and bob.node.node_id = 415 and pipe.id = 1) I am attempting to use the above. However, with or without the enclosing brackets I get a syntax error on the word into. Help will be appreciated. Bob == Your statement is syntactically wrong. If you provide your version and a better idea of what you are trying to accomplish someone may be able to provide meaningful advice. You should also provide the entire query. I assume you are not due to the missing "END" after your CASE construct. There is also no location for "somevariable" to actual come from. As thought starters you can place the INSERT statement into a function OR you can try using WITH ( INSERT RETURNING ); which one if either is workable depends on more information than you have provided. You can also use plpgsql to encapsulate the procedural logic. You can either write a named function or you can use "DO". David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general