[BUG] view ddl fail to show for PostgreSQL

[elsha-phytech]

• Database type and version:

SELECT version();

version
PostgreSQL 14.10 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 9.5.0, 64-bit

When trying to get a view structure (ddl) in PG DB I get a blank result:

I went on and did a little skim through the code. This is the lines I've found:

vscode-database-client/src/service/dialect/postgreSqlDialect.ts

Lines 140 to 142 in da698b5

	showViewSource(database: string, table: string): string {
	return `SELECT CONCAT('CREATE VIEW ',table_name,'\nAS\n(',regexp_replace(view_definition,';$',''),')') "Create View",table_name,view_definition from information_schema.views where table_schema='${database}' and table_name='${table}';`
	}

I think this syntax is the problem, and should be changed to:

showViewSource(database: string, table: string): string {
        return `select pg_get_viewdef('${database}.${name}' :: regclass) "Create View",'${name}' "View";`;    }

This type of query seems to work in my case.
You can also use the pg_views view if it prefferd (https://dba.stackexchange.com/a/301076).

Best regards.
Elsha

[cweijan]

Fixed in version 8.3.6.