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
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
Blog: http://raghavt.blogspot.com/
On Sat, May 19, 2012 at 2:10 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
"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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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