Re: Create loop in postgresql

Rainer Pruy <Rainer.Pruy@xxxxxxxxx> · Mon, 03 Sep 2012 18:12:25 +0200



    Try it with parentheses around the (SELECT....).

    
    On 03.09.2012 18:06, Robert Buckley
      wrote:

    
        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