CODEWITHSUNDEEP
postgresqlcommentsindentationpgadmin
wiltomap
wiltomap

Reputation: 4233

How to keep comments inside a view definition with PostgreSQL?

I need to keep the comments I add inside a SQL query when I create views from complex queries, in order to come back to views definition more easily. Within pgAdminIII, when I create a view and then consult the view definition, the comments are deleted and indentation completely revised... Is there a way to change this behaviour?

View creation:

CREATE OR REPLACE VIEW public.v_test AS 

-- Count number of null lines within table 'test'
(SELECT * FROM public.test WHERE client IS NULL);

View definition after creation, as displayed in pgAdminIII:

-- View: v_test

-- DROP VIEW v_test;

CREATE OR REPLACE VIEW v_test AS 
 SELECT test.gid,
    test.client
   FROM test
  WHERE test.client IS NULL;

ALTER TABLE v_test
  OWNER TO postgres;

Thanks for help!

Upvotes: 6

Views: 3861

Answers (4)

Ostori
Ostori

Reputation: 1

I've been working in Postgres for about a year and have also become ultimately frustrated with the absence of comments in views. But I think I came up with a kind of solution. For ON/WHERE clauses one can use this construction that always returnes TRUE:

WHERE 'Here it filters some columns on conditions' NOTNULL AND ...

For calculations in SELECT one can turn it to 1:

SELECT ('Here it calculates some column' NOTNULL)::integer * ...

And so we end up with smth like:

SELECT ('Finding average column1 among column2' notnull)::integer * 
SUM(column1)/count(distinct column2)
FROM table
WHERE 'Only records with dates in column3 older than 1 year' NOTNULL AND
column3 < now() - interval '1 year'

I'm not a skilled DB engineer and so maybe I don't see possible implications at the moment. Although it's simple and it's better than nothing, I didn't find any similar solutions on the internet, so I'm expecting this idea to not be so smart and easy)

Any thoughts? I'd appreciate it)

Upvotes: 0

Tyler Brown
Tyler Brown

Reputation: 151

Another option would be to create a late binding view. To do so, use WITH NO SCHEMA BINDING when creating your view. This may have implications in your environment so check out NO SCHEMA BINDING here: CREATE VIEW DOCS

Example 1:

CREATE VIEW test_1 AS
--selects the number 1
SELECT 1;

Example 2:

CREATE VIEW test_2 AS
--selects the number 2
SELECT 2
WITH NO SCHEMA BINDING;

Because of the late binding, among other things, example 2's comments will persist when viewing the view definition. Example 1 will not persist comments.

Upvotes: -1

klin
klin

Reputation: 121604

Postgres doesn't store a view definition as is, hence you cannot store comments this way. Use the comment command:

create view my_view as select 1;
comment on view my_view is 'It is my view';

select obj_description('my_view'::regclass);

 obj_description 
-----------------
 It is my view
(1 row)

You can see the comment in PgAdmin3:

-- View: public.my_view

-- DROP VIEW public.my_view;

CREATE OR REPLACE VIEW public.my_view AS 
 SELECT 1;

ALTER TABLE public.my_view
  OWNER TO postgres;
COMMENT ON VIEW public.my_view
  IS 'it is my view';

Upvotes: 8

jlee88my
jlee88my

Reputation: 3043

No, Postgres saves views as parse trees, so it does not remember whitespace or comments.

However, if you really need it, functions can remember comments.

Upvotes: 11

Related Questions