From: | "Scott Schulthess" <scott(at)topozone(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | returning setof varchar |
Date: | 2007-04-20 16:00:19 |
Message-ID: | 4BF377919225F449BB097CB76FFE9BC801988008@ptolemy.topozone.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.htm
l
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
From | Date | Subject | |
---|---|---|---|
Next Message | RPK | 2007-04-20 16:22:21 | Schema relationship diagram |
Previous Message | Tom Lane | 2007-04-20 15:21:56 | Re: Building PG 8.2.3 for x86_64 on Mac OS X 10.4.9 |