As Tom said, you need to declare tmp_var as per the result set coming from select distinct (value) column.I gave a try on it.
create or replace function prn_test() returns void as $$
declare
tmp_var
test_table.name%type; ///Test_table with name column which is varchar(20) in my case
begin
for tmp_var in (select distinct name from test_table)
loop
raise notice 'Give anything here :) ... !!!';
update test_table set name=tmp_var;
end loop;
end;
$$ language plpgsql;
---
Regards,
Raghavendra
EnterpriseDB Corporation
On Sat, May 19, 2012 at 2:10 AM, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
"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
