returning setof varchar

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

Responses

Browse pgsql-general by date

  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