On Wed, 2 Apr 2008 23:54:18 -0300
"Osvaldo Kussama" <osvaldo.kussama@gmail.com> wrote:
> 2008/4/2, Ivan Sergio Borgonovo <mail@webthatworks.it>:
> > I've
> >
> > create table types(
> > typeid int,
> > special boolean not null
> > );
> >
> > create table methods(
> > methodid int,
> > typeid references types(typeid),
> > );
> >
> > create table orders(
> > orderid int
> > );
> >
> > create table order_payments(
> > payid int
> > orderid references order(orderid),
> > methodid references method(methodid),
> > issued boolean not null default false
> > );
> >
> > orderid payid methodid special
> > 1 1 1 t
> > 1 2 2 t
> > 1 3 3 t
> > 1 4 4 f
> > 1 5 4 f
> >
> > I'd like to chose one payid
> > If the payid is "special" just set issued to true for that payid,
> > leave the other unchanged.
> > If the payid is not "special" set issued for all the payid in the
> > same order.
> >
> > eg.
> > So if payid=4 I'd have
> >
> > orderid payid methodid special issued
> > 1 1 1 t t
> > 1 2 2 t t
> > 1 3 3 t t
> > 1 4 4 f t
> > 1 5 4 f t
> >
> > and if payid=2
> >
> > orderid payid methodid special issued
> > 1 1 1 t f
> > 1 2 2 t t
> > 1 3 3 t f
> > 1 4 4 f f
> > 1 5 4 f f
> >
> > This stuff below doesn't work:
> >
> > update order_payments
> > set issued=true where payid in (
> > select p.payid
> > from order_payments p
> > join methods as m on m.methodid=p.methodid
> > join types as t on m.typeid=t.typeid
> > where (p.orderid=%d and not t.special) or p.payid=%d);
> >
> > and I can understand why but I can't rewrite it to make it work.
> >
>
> Try:
> UPDATE order_payments
> SET issued=true FROM methods m, types t
> WHERE m.methodid=p.methodid AND
p -> order_payments
> m.typeid=t.typeid AND
> ((order_payments.orderid=%d AND NOT t.special) OR
> order_payments.payid=%d));
one less )
Even after correcting the few typos this version obtain the same
result of
update order_payments set issued=true where payid=%d
I ended up in writing a plpgsql function that retrieve special and
then have an if block.
create or replace function IssuePay(_PayID int,out _OrderGroupID bigint, out _Online boolean)as$$begin select into
_OrderGroupID,_OnLine p.OrderGroupID, t.OnLine from shop_commerce_ordergroup_pay p join shop_commerce_paymethods m on
p.PayMethodID=m.MethodID join shop_commerce_paytypes t on m.TypeID=t.TypeID where PayID=_PayID; if(_OnLine) then
updateshop_commerce_ordergroup_pay set Issued=true where PayID=_PayID; else update shop_commerce_ordergroup_pay
setIssued=true where OrderGroupID=_OrderGroupID; end if; return;end;$$ language plpgsql;
mutatis mutandis.
It may not be the most elegant thing but it is enough encapsulated it
won't be a pain to refactor once I become a better DBA or someone
else point out a better solution on the list.
I'd be curious if it had a performance penalty over a one update
statement.
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
