Re: Create loop in postgresql

Robert Buckley <robertdbuckley@xxxxxxxxx> · Mon, 3 Sep 2012 18:42:47 +0100 (BST)

This works ok 
SELECT round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2) FROM energie.tennet_auswertung_2010

but this does not

update energie.tennet_auswertung_2010 set "Test"=
(
SELECT round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2) FROM energie.tennet_auswertung_2010
);

?????????

  Von: Robert Buckley <robertdbuckley@xxxxxxxxx>
 An: salah jubeh <s_jubeh@xxxxxxxxx>; "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx> 
 Gesendet: 19:30 Montag, 3.September 2012
 Betreff: Re:  Create loop in postgresql

Now even stranger is that I can´t update a column with the query.

Update energie.tennet_auswertung_2010 set "Test"=(

SELECT round(100*ges_kw_zgb/total.totalsum, 2) from energie.tennet_auswertung_2010, 
(select sum(ges_kw_zgb) totalsum From
 energie.tennet_auswertung_2010) as total
);

ERROR:  more than one row returned by a subquery used as an _expression_

But If I use Insert as below it DOES work!

insert into energie.tennet_auswertung_2010("Test") SELECT round(100*ges_kw_zgb/total.totalsum, 2) 
from energie.tennet_auswertung_2010, (select sum(ges_kw_zgb) totalsum From energie.tennet_auswertung_2010) as total;

How would I update the rows?

Cheers for any help,

Rob

  Von: salah jubeh <s_jubeh@xxxxxxxxx>
 An: Robert Buckley <robertdbuckley@xxxxxxxxx>; "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx> 
 Gesendet: 18:23 Montag, 3.September 2012
 Betreff: Re:  Create loop in postgresql

Hello Robert,

I just gave an
 example and this also can be optimized . but let me first clarify one thing here. 

since you have the same behaviour for all values , there is no need to use case in the first place. So just drop it.

I think below would be the correct syntax

select name,ges_kw_zgb, (SELECT round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2)) FROM
 energie.tennet_auswertung_2010;

Regards

  From: Robert Buckley <robertdbuckley@xxxxxxxxx>

 To: salah jubeh <s_jubeh@xxxxxxxxx>; "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx> 
 Sent: Monday, September 3, 2012 6:06 PM
 Subject: Re: [GENERAL] Create loop in postgresql

this give an error.

select name,ges_kw_zgb, SELECT round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From
 energie.tennet_auswertung_2010)),2);

ERROR:  syntax error at or near "SELECT"
LINE 2: select name,ges_kw_zgb, SELECT round(100 * (ges_kw_zgb / (se...

  Von: salah jubeh <s_jubeh@xxxxxxxxx>
 An: Robert Buckley <robertdbuckley@xxxxxxxxx>;
 "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx> 
 Gesendet: 17:45 Montag, 3.September 2012
 Betreff: Re:  Create loop in postgresql

I am wondering why do not you write it like this 

select name,ges_kw_zgb, select round(100 *
 (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2) ...... 

Regards

  From: Robert Buckley <robertdbuckley@xxxxxxxxx>
 To: "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx> 
 Sent: Monday, September 3, 2012 5:30 PM
 Subject:  Create loop in postgresql

Hi,

I am trying to loop through the records in a table and update a column. I can do this with a case statement but I would like to simplify this to a simple loop statement. I can´t seem to work out how to do it though.

Here is the
 case statement.

select name,ges_kw_zgb,
case
When name='Bad Harzburg' then (select round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2))
When name='Braunlage' then (select round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2))
When name='Braunschweig' then (select round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2))
When name='Büddenstedt' then (select round(100 * (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2))

End as z

from energie.tennet_auswertung_2010
;

 Instead of having to write the name variable in the function, I would just like to iterate through each record and execute the select round(100 *
 (ges_kw_zgb / (select sum(ges_kw_zgb) From energie.tennet_auswertung_2010)),2) command. 

If anyone can help I´d me grateful,

cheers,

Rob