execute immediate select vertica

hi,
i got an table like this one:
schema_name | table_name | col
and i want to create an view on the database like this:
select *, (select max(col) from schema_name.table_name) as max
from table;

Tagged:

Answers

  • SruthiASruthiA Employee

    What is col? Is it the list of columns for the corresponding table or count of columns?

  • Bryan_HBryan_H Employee

    If you are looking for information on DDL like schemas, tables, columns, have a look at the system table v_monitor.column_storage

  • Jim_KnicelyJim_Knicely Administrator
    edited February 3

    I wonder if this is the intention?

    dbadmin=> SELECT * FROM like_this_one;
     schema_name | table_name | col
    -------------+------------+-----
     public      | test       | c
     public      | test2      | c
    (2 rows)
    
    dbadmin=> SELECT * FROM public.test;
     c
    ---
     1
     2
     3
    (3 rows)
    
    dbadmin=> SELECT * FROM public.test2;
     c
    ----
      1
      5
     10
    (3 rows)
    
    dbadmin=> \! vsql -Atc "SELECT 'CREATE OR REPLACE VIEW public.table_max_values AS ' UNION ALL SELECT 'SELECT ''' || schema_name || ''' AS schema_name, ''' || table_name || ''' AS table_name, ''' || col || ''' AS col, (SELECT MAX(' || col || ') FROM ' || schema_name || '.' || table_name || ') AS max' || NVL2(LEAD(schema_name) OVER (ORDER BY 1), ' UNION ALL', ';') FROM like_this_one;" | vsql
    CREATE VIEW
    
    dbadmin=> SELECT * FROM public.table_max_values;
     schema_name | table_name | col | max
    -------------+------------+-----+-----
     public      | test       | c   |   3
     public      | test2      | c   |  10
    (2 rows)
    

    Problem here is that the view needs to be "refreshed" (i.e. re-created periodically) to get up tp date MAX values.

    Fyi.. Similar topic:
    https://www.vertica.com/blog/table-row-counts-by-schema/

  • moshegmosheg Employee

    Hello Roy,
    As per your request using the similar topic Jim mentioned above, please let us know if the following answer your request
    Also to query the view in the same one command statement:

    vsql -f tables_rows_count.sql
    CREATE VIEW
          table_schema         | total_row_count
        ----------------------+-----------------
         DEMO_CODE_1     |                               4
          grasp                         |                     97090
         online_sales            |                1110000
         public                         |     10012113201
         store                          |     11000001000
        (5 rows)
    
    cat tables_rows_count.sql
    \! vsql -XAtc "SELECT 'CREATE OR REPLACE VIEW public.my_tables AS SELECT table_schema, SUM(table_row_count) total_row_count FROM ('UNION ALL SELECT 'SELECT ''' || table_schema || ''' AS table_schema, (SELECT COUNT(1) FROM ' || table_schema || '.' || table_name || ') AS table_row_count' || DECODE(LEAD(table_name) OVER (ORDER BY 1), NULL, ') foo GROUP BY table_schema ORDER BY total_row_count;', ' UNION ALL') FROM tables WHERE table_definition = '';" | vsql && vsql -Xc "SELECT * FROM public.my_tables;"
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.