Обсуждение: A function which returns all rolname from pg_roles.

How do I make this function work?  I am trying to get all the rolnames
from pg_roles.

CREATE OR REPLACE FUNCTION test() SETOF name AS $$
DECLARE
rrol  name;
BEGIN
SELECT rolname INTO rrol FROM pg_roles;
RETURN setof rrol;
END;
$$ LANGUAGE plpgsql;


regards,

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

am  10.07.2006, um 19:27:38 -0700 mailte Karen Hill folgendes:
> How do I make this function work?  I am trying to get all the rolnames
> from pg_roles.
>
> CREATE OR REPLACE FUNCTION test() SETOF name AS $$
> DECLARE
> rrol  name;
> BEGIN
> SELECT rolname INTO rrol FROM pg_roles;
> RETURN setof rrol;
> END;
> $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_all_roles() returns setof text as $$
declare
        rrol    record;
begin
        for rrol in select rolname from pg_roles loop
                return next rrol.rolname;
        end loop;
end;
$$ language plpgsql;


select * from get_all_roles();



HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===