how to for loop with distinct values? - Mailing list pgsql-general

From J.V.
Subject how to for loop with distinct values?
Date
Msg-id 4FBAA80C.5090308@gmail.com
Whole thread Raw
Responses Re: how to for loop with distinct values?
List pgsql-general

I am banging my head over this.  I want to select distinct values from a varchar column and iterate through the values.

I want to select the distinct values from this column and loop through them (using as a variable) in a raise notice statement and also in an update statement.

I have not been able to do this trying dozens of different approaches.  I could not find an example even after searching google.


So for example, suppose table: mytable has a column "value" that is defined as a varchar:


    for tmp_var in select distinct(value) from mytable where value2='literal'
    loop
        raise notice 'I want to print a message here - the tmp_var is [' || tmp_var || ']';   <== error on this line
        update table set somecolumn = ''' || tmp_var || '''
    end loop;

I want to use each distinct value in a "raise notice" line and an update statement.


tmp_var has to be in  ' ' ticks or will not work.  it is failing on the first FOR statement stating:  "invalid input syntax for integer: "some_distinct_value".

How do I select varchar distinct values and iterate using variables in a raise notice statement and inside another update statement?


this seems simple to do , but have not found a way.


pgsql-general by date:

Previous
From: Dmitriy Igrishin
Date:
Subject: Re: Confusion about composite indexes
Next
From: Lonni J Friedman
Date:
Subject: significant performance hit whenever autovacuum runs after upgrading from 9.0 -> 9.1