Всегда после создания функции выдаю права на выполнение
grant execute on function client_addr_pg_stat_replication() to user;
Делаю функцию
CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS text AS $$
BEGIN
PERFORM client_addr from pg_stat_replication;
END;
$$ LANGUAGE plpgsql security definer;
Результат:
select * from client_addr_pg_stat_replication();
ERROR: control reached end of function without RETURN
CONTEXT: PL/pgSQL function client_addr_pg_stat_replication()
CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS SETOF RECORD AS $$
BEGIN
select client_addr from pg_stat_replication;
END;
$$ LANGUAGE plpgsql security definer;
Результат:
select * from client_addr_pg_stat_replication();
ERROR: a column definition list is required for functions returning "record"
LINE 1: select * from client_addr_pg_stat_replication();
CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication(TEXT)
RETURNS setof pg_stat_replication
AS
$BODY$
BEGIN
RETURN QUERY SELECT client_addr FROM pg_stat_replication;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
Результат:
select * from client_addr_pg_stat_replication(client_addr);
ERROR: column "client_addr" does not exist
LINE 1: select * from client_addr_pg_stat_replication(client_addr);
CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS TABLE(client_addr varchar) AS
$$
BEGIN
SELECT client_addr FROM pg_stat_replication;
END;
$$ LANGUAGE plpgsql security definer;
Результат:
select * from client_addr_pg_stat_replication();
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_replication() line 3 at SQL statement
CREATE OR REPLACE FUNCTION public.client_addr_pg_stat_replication(varchar)
RETURNS setof pg_stat_replication
AS
$BODY$
BEGIN
RETURN QUERY SELECT * FROM pg_stat_replication;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
Результат:
select * from client_addr_pg_stat_replication();
ERROR: function client_addr_pg_stat_replication() does not exist
LINE 1: select * from client_addr_pg_stat_replication();
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
redcheckpg=> select * from client_addr_pg_stat_replication(client_addr);
ERROR: column "client_addr" does not exist
LINE 1: select * from client_addr_pg_stat_replication(client_addr);