I'm reading the PostgreSQL chapter of Seven Databases in Seven Weeks.
Yesterday the book asked me to create a view in psql using this command:
CREATE VIEW holidays AS SELECT event_id AS holiday_id, title AS name, starts AS date FROM events WHERE title LIKE '%Day%' AND venue_id IS NULL;
Today the book asks me to add a column to the view. It gives a complete
CREATE OR REPLACE VIEW command for doing that.
I was feeling lazy, so to save typing I tried to copy and modify the existing view definition. This approach works well in SQL Server because the server keeps the exact text used to create the view.
The 'view definition' output of
\d+ holidays surprised me:
SELECT events.event_id AS holiday_id, events.title AS name, events.starts AS date FROM events WHERE events.title ~~ '%Day%'::text AND events.venue_id IS NULL;
The output is a 'decompiled reconstruction' rather than the original text. (See System Information Functions in the manual.)
That's fine, but it replaced the standard SQL expression
events.title LIKE '%Day%' with the PostgreSQL non-standard equivalent
events.title ~~ '%Day%'::text'.
I'd rather use standard SQL syntax because it's more familiar and portable.
Is there a way to tell PostgreSQL to script the view using equivalent standard syntax?