2012/5/22 Merlin Moncure <mmonc...@gmail.com> > On Mon, May 21, 2012 at 3:39 PM, J.V. <jvsr...@gmail.com> wrote: > > > > 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. > > Well it looks like you have a couple of problems here. First, when you > 'raise notice', generally you do it like this: > raise notice 'value of var is %', var; > > And not do string concatenation. As for the update statement, you should > be quoting tmp_var. At most you should be casting (tmp_var::int) and then > be diagnosing why you have non integer data in a value you are trying to > put into a integer column (which is the point of the insert). So, you are > very close -- it all comes down to how you are handling the NOTICE i think. > A quick review of the examples here: > http://www.postgresql.org/docs/9.2/static/plpgsql-errors-and-messages.html > might be helpful. > > merlin > > Nice color and font ;-)
-- // Dmitriy.
