public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jim Jones <[email protected]>
To: Michael Paquier <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: David Zhang <[email protected]>
Subject: Re: [PATCH] Add inline comments to the pg_hba_file_rules view
Date: Fri, 15 Sep 2023 09:37:23 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

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?

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: [PATCH] Add inline comments to the pg_hba_file_rules view
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox