How do I get this to work?
create or replace function getquadalphabet(text) returns setof varchar as $$
declare r varchar;
begin
for r in SELECT distinct(substring(drgtitle, 1, 1)) as text from stockdrgmeta where state ilike '%' || $1 || '%'
LOOP
return next r;
END LOOP;
end;
$$ language plpgsql;
loop variable of loop over rows must be record or row variable at or near "LOOP" at character 218
The documentation says
http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html
Note: The PL/pgSQL parser presently distinguishes the two kinds of FOR loops (integer or query result) by checking whether .. appears outside any parentheses between IN and LOOP. If .. is not seen then the loop is presumed to be a loop over rows. Mistyping the .. is thus likely to lead to a complaint along the lines of "loop variable of loop over rows must be a record or row variable or list of scalar variables", rather than the simple syntax error one might expect to get.
So where do I put the “..” so that r can be of varchar or text?
If I make R a record, it returns something like (A) instead of A – you know what I mean?
All I want to do is return a setof text or varchar!
Thanks!
-Scott