On 15.09.23 01:28, Michael Paquier wrote:
> Yes, my suggestion was to define a new set-returning function that
> takes in input a file path and that returns as one row one comment and
> its line number from the configuration file.
> --
> Michael
Thanks!
If reading the file again is an option, perhaps a simple SQL function
would suffice?
Something along these lines ..
CREATE OR REPLACE FUNCTION pg_read_conf_comments(text)
RETURNS TABLE (line_number int, comment text) AS $$
SELECT lnum,
trim(substring(line,
nullif(strpos(line,'#'),0)+1,
length(line)-strpos(line,'#')
)) AS comment
FROM unnest(string_to_array(pg_read_file($1),E'\n'))
WITH ORDINALITY hba(line,lnum)
WHERE trim(line) !~~ '#%' AND trim(line) <> '';
$$
STRICT LANGUAGE SQL ;
.. then we could join it with pg_hba_file_rules (or any other conf file)
SELECT type, database, user_name, address, c.comment
FROM pg_hba_file_rules h, pg_read_conf_comments(h.file_name) c
WHERE user_name[1]='jim' AND h.line_number = c.line_number ;
type | database | user_name | address | comment
------+----------+-----------+-----------+---------
host | {db} | {jim} | 127.0.0.1 | foo
host | {db} | {jim} | 127.0.0.1 | bar
host | {db} | {jim} | 127.0.0.1 | #foo#
(3 rows)
Is it more or less what you had in mind?