Re: A function which returns all rolname from pg_roles. - Mailing list pgsql-general

Karen Hill wrote:
> How do I make this function work?  I am trying to get all the rolnames
> from pg_roles.
>

Why not just do "SELECT rolname FROM pg_roles;"?

Anyway, in PL/pgSQL:

CREATE OR REPLACE FUNCTION test() RETURNS SETOF name AS $$
   DECLARE
     rec  record;
   BEGIN
     FOR rec IN SELECT rolname FROM pg_roles LOOP
       RETURN NEXT rec.rolname;
     END LOOP;
   END;
$$ LANGUAGE plpgsql;

And simpler as an SQL function (although, again, why bother?):

CREATE OR REPLACE FUNCTION test_sql() RETURNS SETOF name AS $$
     SELECT rolname FROM pg_roles
$$ LANGUAGE sql;

HTH,

Joe