\pset null '(null)'

-- Scenario 1: new table must have comment
drop table if exists test1, test2;7

create table test1 (field int);
comment on table test1 is 'Simple Comment';

create table test2 (like  test1 including comments);

SELECT obj_description('test2'::regclass, 'pg_class') AS table_comment;

/* output:
 table_comment
----------------
 Simple Comment
(1 row)
*/

-- Scenario 2: new table must not have any comments
create table test1 (field int);
comment on table test1 is 'Simple Comment';

create table test2 (like test1); 
SELECT obj_description('test2'::regclass, 'pg_class') AS table_comment;

/* output:
 table_comment
---------------
 (null)
(1 row)
*/

-- Scecnario 3: Multiple tables

create table test1 (field1 int);
comment on table test1 is 'Comment 1';

create table test2 (field2 int);
comment on table test2 is 'Comment 2';

create table test3 (field3 int);
comment on table test3 is 'Comment 3';

create table test4 (
  like test1 including comments,
  like test2 including comments,
  like test3 including comments
 );
 
SELECT obj_description('test4'::regclass, 'pg_class') AS table_comment;


/* output
table_comment
---------------
 Comment 1    +
 Comment 2    +
 Comment 3
(1 row)
*/

-- Scecnario 4: Multiple tables with a table with empty comment

create table test1 (field1 int);
comment on table test1 is 'Comment 1';

create table test2 (field2 int);

create table test3 (field3 int);
comment on table test3 is 'Comment 3';

create table test4 (
  like test1 including comments,
  like test2 including comments,
  like test3 including comments
 );
 
SELECT obj_description('test4'::regclass, 'pg_class') AS table_comment;

/* output
table_comment
---------------
 Comment 1    +
 Comment 3
(1 row)
*/

-- Scecnario 5: Multiple tables with changing LIKE order

create table test1 (field1 int);
comment on table test1 is 'Comment 1';

create table test2 (field2 int);
comment on table test2 is 'Comment 2';

create table test3 (field3 int);
comment on table test3 is 'Comment 3';

create table test4 (field4 int);
comment on table test4 is 'Comment 4';

create table test5 (
  like test2 including comments,
  like test1 including comments,  
  like test3 including comments,
  like test4 including comments
 );
 
SELECT obj_description('test5'::regclass, 'pg_class') AS table_comment;

/* output
 table_comment
---------------
 Comment 2    +
 Comment 1    +
 Comment 3    +
 Comment 4
(1 row)
*/

-- Scecnario 5: Multiple same kind options

create table test1 (field1 int);
comment on table test1 is 'Single comment';

create table test2 (like test1 including comments excluding comments);

SELECT obj_description('test2'::regclass, 'pg_class') AS table_comment;

/* output
 table_comment
---------------
 (null)
(1 row)
*/

create table test3 (like test1 including comments excluding comments including comments);

SELECT obj_description('test3'::regclass, 'pg_class') AS table_comment;

/* output
 table_comment
----------------
 Single comment
(1 row)
*/

create table test4 (like test1 including ALL excluding comments);

SELECT obj_description('test4'::regclass, 'pg_class') AS table_comment;

/* output
 table_comment
---------------
 (null)
(1 row
*/

create table test5 (like test1 excluding comments including ALL);

SELECT obj_description('test5'::regclass, 'pg_class') AS table_comment;

/* output
 table_comment
----------------
 Single comment
(1 row)
*/

-- Scecnario 6: Comments with new line

create table test1 (field1 int);
comment on table test1 is 'line 1
line 2
and line 3';

create table test2 (like test1 including comments);

SELECT obj_description('test2'::regclass, 'pg_class') AS table_comment;

/* output
 table_comment
---------------
 line 1       +
 line 2       +
 and line 3
(1 row)
*/

-- Scecnario 7: Multiple tables with comments with line break

create table test1 (field1 int);
comment on table test1 is 'Comment 1
line 2';

create table test2 (field2 int);
comment on table test2 is 'Comment 2
line 2';

create table test3 (like test1 including comments, like test2 including comments);

SELECT obj_description('test3'::regclass, 'pg_class') AS table_comment;

/* output
 table_comment
---------------
 Comment 1    +
 line 2       +
 Comment 2    +
 line 2
(1 row)
*/

-- Schenario 7: Stress like statement (1000 tables)
CREATE OR REPLACE PROCEDURE stress_comments(qtd_tables int)
LANGUAGE plpgsql
AS $$
DECLARE
  like_cmd text;
BEGIN
    FOR i IN 1..qtd_tables LOOP
        execute 'drop table if exists test'||i;
    END LOOP;
	
	execute 'drop table if exists stress_comments';
	
	
	like_cmd := '';
	
	FOR i IN 1..qtd_tables LOOP
        execute format('CREATE TABLE %s (%s int)', 'test'||i, 'field'||i);
		execute format('comment on TABLE %s is ''Comment %s'' ', 'test'||i, i);
		like_cmd := like_cmd || 'LIKE test'||i||' including comments,';
    END LOOP;
	
	like_cmd := rtrim(like_cmd, ',');
	
	execute format('create table stress_comments (%s)', like_cmd);
	
	-- Cleanup
	FOR i IN 1..qtd_tables LOOP
        execute 'drop table if exists test'||i;
    END LOOP;
END;
$$;

call stress_comments(1000);

SELECT obj_description('stress_comments'::regclass, 'pg_class') AS table_comment;

/* Output:
 table_comment
---------------
 Comment 1    +
 Comment 2    +
 Comment 3    +
 Comment 4    +
 Comment 5    +
 Comment 6    +
 Comment 7    +
 Comment 8    +
 Comment 9    +
 Comment 10   +
 Comment 11   +
...
*/

-- Scenario 8: \dt+ command

create table test1 (field int);
comment on table test1 is 'Simple Comment';

create table test2 (like  test1 including comments);

\dt+ test2

/* Output:
                                       List of tables
 Schema | Name  | Type  |  Owner   | Persistence | Access method |  Size   |  Description
--------+-------+-------+----------+-------------+---------------+---------+----------------
 public | test2 | table | postgres | permanent   | heap          | 0 bytes | Simple Comment
(1 row)
*/


-- Scenario 9: Columns, contraints and indexes comments checking whit pg_dump

create table test1 (field1 int constraint nn_cons not null, field2 int);
comment on table test1 is 'comment on table';
comment on column test1.field1 is 'comment on column';
comment on constraint nn_cons on test1 is 'comment on not null constraint';
alter table test1 add constraint chk_cons check (field1 > 0);
comment on constraint chk_cons on test1 is 'comment on chk constraint';

create index idx_test1 on test1 (field1);
comment on index idx_test1 is 'comment on index';

create statistics stat_test1 on field1, field2 from test1;
comment on statistics stat_test1 is 'comment on statistic';

create table test2 (like  test1 including ALL);

pg_dump postgres > output.sql

/* output (fragments):
CREATE TABLE public.test2 (
    field1 integer CONSTRAINT nn_cons NOT NULL,
    field2 integer,
    CONSTRAINT chk_cons CHECK ((field1 > 0))
);

--
-- Name: TABLE test2; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.test2 IS 'comment on table';


--
-- Name: COLUMN test2.field1; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN public.test2.field1 IS 'comment on column';

--
-- Name: CONSTRAINT nn_cons ON test2; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON CONSTRAINT nn_cons ON public.test2 IS 'comment on not null constraint';

--
-- Name: CONSTRAINT chk_cons ON test2; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON CONSTRAINT chk_cons ON public.test2 IS 'comment on chk constraint';

--
-- Name: test2_field1_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX test2_field1_idx ON public.test2 USING btree (field1);


COMMENT ON INDEX public.test2_field1_idx IS 'comment on index';

--
-- Name: test2_field1_field2_stat; Type: STATISTICS; Schema: public; Owner: postgres
--

CREATE STATISTICS public.test2_field1_field2_stat ON field1, field2 FROM public.test2;

--
-- Name: STATISTICS test2_field1_field2_stat; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON STATISTICS public.test2_field1_field2_stat IS 'comment on statistic';
*/


