Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to for loop with distinct values?

how to for loop with distinct values?

From
"J.V."
Date:

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.

Re: how to for loop with distinct values?

From
Merlin Moncure
Date:
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

Re: how to for loop with distinct values?

From
Dmitriy Igrishin
Date:


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.


Re: how to for loop with distinct values?

From
Merlin Moncure
Date:
On Tue, May 22, 2012 at 6:03 AM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
>
> Nice color and font ;-)
>

yup -- html formatted emails that I find displeasing get an automatic
response in 'ms comic sans' :-D.

merlin