The important thing here to see is how PostgreSQL handles the view. View_demo=# CREATE VIEW v AS SELECT * FROM t_product The first thing to do in order to get our demo going is to create a view: Id | integer | | not null | nextval('t_product_id_seq'::regclass) Here is the layout of the table:Ĭolumn | Type | Collation | Nullable | Default My table has just three simple columns and does not contain anything special. To show what PostgreSQL will do, I created a simple table: What happens if the data structure of the underlying table changes? CREATE VIEW in PostgreSQL The question many people now ask if: If a view is based on a table. The fields in a view are fields from one or more real tables in the database. It is an abstraction layer, which allows to access the result of a more complex SQL fast an easily. In this tutorial, we have shown you how to create, alter, and remove PostgreSQL views.In PostgreSQL, a view is a virtual table based on an SQL statement. The view customer_infois removed from the database. To avoid this, you normally add IF EXISTS option to the statement to instruct PostgreSQL to remove the view if it exists, otherwise, do nothing.įor example, to remove the customer_info view that you have created, you execute the following query: DROP VIEW IF EXISTS customer_info Code language: SQL (Structured Query Language) ( sql ) Removing a view that does not exist in the database will result in an error. You specify the name of the view that you want to remove after DROP VIEW clause. To remove an existing view in PostgreSQL, you use DROP VIEW statement as follows: DROP VIEW view_name Code language: SQL (Structured Query Language) ( sql ) For detailed information on the altering view’s definition, check it out the PostgreSQL ALTER VIEW statement. PostgreSQL allows you to set a default value for a column name, change the view’s schema, set or reset options of a view. For example, you can change the name of the view from customer_master to customer_info by using the following statement: ALTER VIEW customer_master RENAME TO customer_info Code language: SQL (Structured Query Language) ( sql ) To change the definition of a view, you use the ALTER VIEW statement. Now, if you select data from the customer_master view, you will see the email column at the end of the list. However, PostgreSQL allows you to append additional columns at the end of the column list.įor example, you can add an email to the customer_master view as follows: CREATE VIEW customer_master AS To be more specific, the new columns must have the same names, the same data types, and in the same order as they were created. The query must generate the same columns that were generated when the view was created. If you try to do it, you will get an error message: “ ERROR: cannot drop columns from view”. PostgreSQL does not support removing an existing column in the view, at least up to version 9.4. To change the defining query of a view, you use the CREATE VIEW statement with OR REPLACE addition as follows: CREATE OR REPLACE view_nameĪS query Code language: SQL (Structured Query Language) ( sql ) This query produces the same result as the complex one with the joins above. INNER JOIN country USING (country_id) Code language: PHP ( php )įrom now on, whenever you need to get complete customer data, you just query it from the view by executing the following simple SELECT statement: SELECTĬustomer_master Code language: SQL (Structured Query Language) ( sql ) However, you can create a view named customer_master as follows: CREATE VIEW customer_master AS The result of the query is as shown in the screenshot below: INNER JOIN country USING (country_id) Code language: SQL (Structured Query Language) ( sql ) If you want to get complete customers data, you normally construct a join statement as follows: SELECT cu.customer_id AS id,Ĭu.first_name || ' ' || cu.last_name AS name,ĬASE WHEN cu.activebool THEN 'active' ELSE '' END AS notes, PostgreSQL CREATE VIEW exampleįor example, in our sample database, we have four tables: A query can be a simple SELECT statement or a complex SELECT statement with joins. The simplest syntax of the CREATE VIEW statement is as follows: CREATE VIEW view_name AS query Code language: SQL (Structured Query Language) ( sql )įirst, you specify the name of the view after the CREATE VIEW clause, then you put a query after the AS keyword. To create a view, we use CREATE VIEW statement. A view provides a consistent layer even the columns of the underlying table change.Like a table, you can grant permission to users through a view that contains specific data that the users are authorized to see.A view helps simplify the complexity of a query because you can query a view, which is based on a complex query, using a simple SELECT statement.A view can be very useful in some cases such as:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |