Get few ip postgresql slave from pg_stat_replication using non-superuser

Question

I try Get few ip postgresql slave from pg_stat_replication using non-superuser

CREATE OR replace FUNCTION client_addr_pg_stat_replication3 
  () returns setof RECORD 
AS 
  $x$ 
  DECLARE 
    rec RECORD; 
  BEGIN 
    SELECT client_addr 
    FROM   pg_stat_replication 
    INTO   rec; 

  END; 
  $x$ LANGUAGE plpgsql security definer;

GRANT EXECUTE ON FUNCTION client_addr_pg_stat_replication3(SETOF record) TO non-superuser;

select * from client_addr_pg_stat_replication3() as x(a int, b int);
ERROR:  function client_addr_pg_stat_replication3() does not exist
LINE 1: select * from client_addr_pg_stat_replication3() as x(a int,...
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Second function

CREATE OR replace FUNCTION client_addr_pg_stat_replication4 
  () returns TABLE (client_addr cidr) 
AS 
  $func$ 
BEGIN 
  RETURN query 
  SELECT client_addr 
  FROM   pg_stat_replication; 

END $func$ LANGUAGE plpgsql security definer;

GRANT EXECUTE ON FUNCTION client_addr_pg_stat_replication4() TO non-superuser;

select * from client_addr_pg_stat_replication4();
ERROR:  column reference "client_addr" is ambiguous
LINE 1: SELECT client_addr FROM pg_stat_replication
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT client_addr FROM pg_stat_replication
CONTEXT:  PL/pgSQL function client_addr_pg_stat_replication4() line 1 at RETURN QUERY

Answer 1

You need a view not just function, e.g.

CREATE OR replace FUNCTION public.pg_stat_repl() returns setof 
  pg_catalog.pg_stat_replication 
AS 
  $$begin 
    RETURN query 
      ( 
       SELECT * 
       FROM pg_catalog.pg_stat_replication); 
  end$$ LANGUAGE plpgsql security definer; 

CREATE VIEW public.pg_stat_repl AS SELECT * FROM public.pg_stat_repl(); 

GRANT SELECT ON public.pg_stat_repl TO non-superuser;

Then you can as non-superuser

SELECT * FROM public.pg_stat_repl;