Thread: returning setof varchar
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
On Fri, 2007-04-20 at 12:00 -0400, Scott Schulthess wrote:
> 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
>
What you need to do is make "r" a record type, and then return an
attribute of the record in the "return next".
So, something like this:
FOR r IN SELECT ... AS text ... LOOP
RETURN NEXT r.text;
END LOOP;
Regards,
Jeff Davis
Scott Schulthess wrote:
> How do I get this to work?
> for r in SELECT distinct(substring(drgtitle, 1, 1)) as text from
> stockdrgmeta where state ilike '%' || $1 || '%'
>
> loop variable of loop over rows must be record or row variable at or
> near "LOOP" at character 218
DECLARE
r RECORD;
BEGIN
FOR r IN ...
RETURN NEXT r.text
--
Richard Huxton
Archonet Ltd