execute immediate select vertica

roybaranes

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;

SruthiA

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

Bryan_H

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

Jim_Knicely

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/

mosheg

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;"