Start a new topic

Extra parenthesis added to extracted PostgreSQL?

[This topic is migrated from our old forums. The original author name has been removed]

Given a contrived view as shown in PgAdmin III on PostgreSQL 9: CREATE OR REPLACE VIEW customer_state AS SELECT cu.customer_key, cu.customer_id, cal.date_dt, COALESCE(ipv.active, 'N'::bpchar) AS active FROM d_customer cu CROSS JOIN d_calendar cal LEFT JOIN arch_customer a ON a.customer_id::text = cu.customer_id::text AND a.extract_dt = cal.date_dt WHERE cu.business_unit_name::text = 'test'::text AND cal.date_dt >= cu.signupdate AND cal.date_dt = cu.signupdate)) AND(cal.date_dt

[This reply is migrated from our old forums. The original author name has been removed]

Re: Extra parenthesis added to extracted PostgreSQL?
Thanks. It appears the built in function pg_get_viewdef also returns the same format, and I don't know what PgAdmin does differently.
[This reply is migrated from our old forums.]

Re: Extra parenthesis added to extracted PostgreSQL?
Hi Matt, Unfortunately, it would be impossible without a PostgreSQL-specific parser and rules for what to clean up, because we use the view declaration as it is returned by the database. This is the SQL we use: SELECT V.TABLE_SCHEMA, V.TABLE_NAME, V.VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS V WHERE V.TABLE_NAME = '' AND V.TABLE_SCHEMA = '' If you know of a way to get the "clean" declaration, please let us know. Best Regards, Hans