Thread: varchar for loops possible?

I have a table with a varchar column.

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 over 100 things in the last two
hours.  I could not find an example on google.

     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 || ']';
         update table set varcharid = ''' || tmp_var || '''
     end loop;

tmp_var has to be in  ' ' ticks or will not work.  it is failing on the
first FOR statment 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.

"J.V." <jvsrvcs@gmail.com> writes:
>      for tmp_var in select distinct(value) from mytable where
> value2='literal'
> tmp_var has to be in  ' ' ticks or will not work.  it is failing on the
> first FOR statment stating:  "invalid input syntax for integer:
> "some_distinct_value".

Um, how do you have tmp_var declared?  plpgsql seems to be acting
as though it's an integer variable, which is not what you need if
"value" is a varchar.

            regards, tom lane

On 2012-05-18, J.V. <jvsrvcs@gmail.com> wrote:
> I have a table with a varchar column.
>
> 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 over 100 things in the last two
> hours.  I could not find an example on google.
>
>      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 || ']';

raise notice does not take a string argument,
it takes a string-literal-like argument.
you can't use a string expression as the argument to raise notice.

do it like this:

raise notice 'the tmp_var is [%]', tmp_var;

I think this restriction is because the psql compiler needs to parse
the string at compile time to produce the raise-notice bytecode,

--
⚂⚃ 100% natural

On 18/05/2012 21:30, J.V. wrote:
> update table set varcharid = ''' || tmp_var || '''

Others have answered your question, but there's a problem here too; you
don't need the quotes. This statement should be just:

  update table set varcharid = tmp_var;

...assuming that the types match, of course.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie