Re: how to for loop with distinct values?

2012/5/22 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Mon, May 21, 2012 at 3:39 PM, J.V. <jvsrvcs(at)gmail(dot)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.