![]() ![]() This view would be used to have customer name and age from the CUSTOMERS table. | 1 | Ramesh | 32 | Ahmedabad | 2000.00 |įollowing is an example to create a view from the CUSTOMERS table. ExampleĬonsider the CUSTOMERS table having the following records − ![]() You can include multiple tables in your SELECT statement in a similar way as you use them in a normal SQL SELECT query. The basic CREATE VIEW syntax is as follows − To create a view, a user must have the appropriate system privilege according to the specific implementation. Views can be created from a single table, multiple tables or another view. Summarize data from various tables which can be used to generate reports.ĭatabase views are created using the CREATE VIEW statement. Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more. Structure data in a way that users or classes of users find natural or intuitive. Views, which are a type of virtual tables allow users to do the following − A view can be created from one or many tables which depends on the written SQL query to create a view. ![]() A view is actually a composition of a table in the form of a predefined SQL query.Ī view can contain all rows of a table or select rows from a table. In this way you obtain a table that records all the creation tables.A view is nothing more than a SQL statement that is stored in the database with an associated name. WHEN TAG IN ('SELECT INTO','CREATE TABLE','CREATE TABLE AS')ĮXECUTE PROCEDURE t_create_history_func() DROP EVENT TRIGGER t_create_history_trigger ĬREATE EVENT TRIGGER t_create_history_trigger ON ddl_command_end ALTER EVENT TRIGGER t_create_history_trigger DISABLE INSERT INTO public.t_create_history (object_type, schema_name, object_identity, creation_date) SELECT obj.object_type, obj.schema_name, obj.object_identity, now() delete event trigger before dropping functionĭROP EVENT TRIGGER IF EXISTS t_create_history_trigger ĭROP FUNCTION IF EXISTS public.t_create_history_func() ĬREATE OR REPLACE FUNCTION t_create_history_func()įOR obj IN SELECT * FROM pg_event_trigger_ddl_commands () WHERE command_tag in ('SELECT INTO','CREATE TABLE','CREATE TABLE AS') Starting from this discussion my solution was: DROP TABLE IF EXISTS t_create_history CASCADE Ĭreation_date timestamp without time zone I'm trying to follow a different way for obtain this. ![]() P.S: Replace inventory in the database with your table name. ON pg_tables.tablename = inventory.tablenameįor my use-case it is ok because I work with a set of dynamic tables that I need to keep track of. Then you could get advantage of pg_tables to run something like this to get existing table creation dates: SELECT pg_tables.tablename, inventory.created_at INSERT INTO inventory VALUES (1, 'temp_table_1', ' 10:00:00') - We add it into the inventory CREATE TABLE temp_table_1 (id SERIAL) - A dynamic table is created Then, when a table you want to keep track of is created it's added in your inventory. CREATE TABLE inventory (id SERIAL, tablename CHARACTER VARYING (128), created_at DATE) Suppose you have a table inventory in your database where you manage to save the creation date of the tables. I tried a different approach to get table creation date which could help for keeping track of dynamically created tables. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |