CODEWITHSUNDEEP
sqlpostgresqlsql-view
IvoAtanasov
IvoAtanasov

Reputation: 186

Create view in postgres sql with comments

I create a view in postgres sql with sql statement

CREATE OR REPLACE VIEW  {ViewName} as 
Select
.....

I am asking is there any way to create comments for columns in the view. After the view is created, it generates an error when a comment is added to a column :

ERROR: "{ViewName}" is not a table, composite type, or foreign table.

Upvotes: 5

Views: 7700

Answers (2)

Bill
Bill

Reputation: 1

Use a dummy select statement. 

select 'general information for the use of the comments' as c1;

It's clunky but the only way I can find to embed comments. For larger items, I create a commentary in a header.

with query_header as (
    select 'purpose - statement' as c1,
    select 'revision - revised 16 Oct 2019 by WDH' as c2
    select 'owner - contact details' as c3
    select 'lines 234-312 to declutter orders with no valid partnmber' as c4
    select 'join on itemtable changed to left join 23July2018 by WDH' as c5
)

Upvotes: 0

user330315
user330315

Reputation:

To define a comment on a column (or a view) use comment on:

create view some_view
as
select x as col1, y as col2, z as col3
from some_table;

Then: 

comment on view some_view is 'Some View';
comment on column some_view.col1 is 'Originally column X';

Upvotes: 12

Related Questions