Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
It tweaks the code to its own taste. For example, you can try what comes back from a some_column IN (1,2,3) expression. This used to annoy me a lot as you lose comments and formatting during the process. –  dezso Nov 22 '13 at 8:25
add comment

migrated from stackoverflow.com Nov 22 '13 at 5:44

This question came from our site for professional and enthusiast programmers.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.