Using Insert with case

On Wed, Aug 8, 2012 at 8:26 AM, Bob Pawley <rjpawley@shaw.ca> 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

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.

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.

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@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.