Postgresql – Is the “comments” field in pgAdmin 4 a pgAdmin thing, or is it a PostgreSQL feature

You could do this using T-SQL and various string functions, but you will need to account for comments using /**/ and --.

In other words, there is no easy way to get back a stored proc definition stripped of all comments. You would essentially need to build your own parsing tool.

First the author table, that's easy.

CREATE TABLE author (
    authorid   serial  PRIMARY KEY,
    username   text    UNIQUE
);

Now we use a self-referential post table to create the hierarchical structure.

CREATE TABLE posts (
    id           serial  PRIMARY KEY,
    parent_post  int     REFERENCES posts,
    authorid     int     NOT NULL REFERENCES author,
    body         text    NOT NULL
);

Test data...

INSERT INTO author (authorid,username) VALUES
  (0, 'ecarroll'),
  (1, 'jbob');
INSERT INTO posts (id,parent_post,authorid,body) VALUES
  (0, null, 0, 'HALLO WORLD'),
  (1 ,0,    1, 'HALLO EVAN' );

Recursive query to get it working.

WITH RECURSIVE t(chain,author_un,text,id) AS (
   SELECT ARRAY[id], username, body, id
   FROM posts
   INNER JOIN author USING (authorid)
   WHERE parent_post IS NULL
   UNION ALL
     SELECT t.chain||p.id, username, p.body, p.id
     FROM t
     INNER JOIN posts AS p
       ON (t.id = p.parent_post)
     INNER JOIN author AS a
       USING (authorid)
)
SELECT *
FROM t;

 chain | author_un |    text     | id 
-------+-----------+-------------+----
 {0}   | ecarroll  | HALLO WORLD |  0
 {0,1} | jbob      | HALLO EVAN  |  1
(2 rows)

For more information on this method, see my post here

• How to build a table for a private messaging system that supports replies?