how to for loop with distinct values?
*
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. **
*
*
*
On Mon, May 21, 2012 at 3:39 PM, J.V. <jvsrvcs@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
2012/5/22 Merlin Moncure <mmoncure@gmail.com>
On Mon, May 21, 2012 at 3:39 PM, J.V. <jvsrvcs@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.