executing delete in a case statement?

On Tuesday 05 Nov 2002 10:03 am, Jean-Christian Imbeault wrote:

What is the proper way to execute a delete statement (if possible) in a
case statement. I am trying various versions of the following but can't
get it to work:

"select member_id, case when (select count(buy_later) from cart_li where
member_id=cart_id AND buy_later=true) > 0 then (delete from cart_li
where cart_id=10) end from carts"

The "case" is supposed to be returning a value not taking actions. SQL is not
a procedural language like C/Perl/PHP etc.

SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test;

See "functions and operators" in the manual for details.

--
Richard Huxton

Date: Tue, 5 Nov 2002 10:42:21 +0000
From: Richard Huxton <dev@archonet.com>

On Tuesday 05 Nov 2002 10:03 am, Jean-Christian Imbeault wrote:

What is the proper way to execute a delete statement (if possible) in a
case statement.

The "case" is supposed to be returning a value not taking actions. SQL is not
a procedural language like C/Perl/PHP etc.

Actually PostgreSQL ships with a procedural extension of SQL!
Check the docs for PL/pgSQL.

Christoph Dalitz

PS: I guess that your emails will land in the spam folder of most people
due to your email address "mega-bucks" :-)

On Tue, 5 Nov 2002, Jean-Christian Imbeault wrote:

What is the proper way to execute a delete statement (if possible) in a
case statement. I am trying various versions of the following but can't

Not really. Case is a value thing.

get it to work:

"select member_id, case when (select count(buy_later) from cart_li where
member_id=cart_id AND buy_later=true) > 0 then (delete from cart_li
where cart_id=10) end from carts"

BTW: is that really what you want anyway? It seems like that delete will
delete the cart_li rows for cart 10 no matter which cart you found
buy_later rows for.

If you wanted to delete all rows from cart_li where there existed a
buy_later=true cart_li row for member_id=cart_id (and didn't mind a
somewhat expensive query), I think you could do it in a single delete with
an EXISTS subquery, something like, delete from cart_li where exists
(select 1 from cart, cart_li c2 where cart_li.cart_id=member_id and
c2.cart_id=member_id and c2.buy_later=true);

If it's something more complicated, maybe a plpgsql function.