Create and manage VIEWs - Hologres

A view is a virtual table defined by a SQL query. Use views to encapsulate complex query logic so that other users or applications can run a simple SELECT against the view instead of writing the underlying query themselves.

For example, if your data warehouse stores order records and you need your analytics team to access revenue summaries without exposing raw transaction details, create a view that pre-joins and aggregates the relevant tables. The analytics team queries the view; the underlying tables stay protected.

Hologres supports creating views based on internal tables, foreign tables, or other views — including federated views that combine both internal and foreign table data.

Usage notes

Data modification rules

View type	Can modify data?
Single-table view	Yes — changes to the view update the base table, and changes to the base table are reflected in the view. Restrict write access to the view to avoid unintended modifications.
Multi-table view	No — data in multi-table views is read-only.

Cross-schema views and SLPM

If the schema-level permission model (SLPM) is enabled on a database, do not create views that reference tables across schemas. Because different schemas require different permissions under SLPM, cross-schema views cannot be queried.

View authorization

Hologres follows the PostgreSQL security definer mode for view authorization. For a user to query a view, both of the following conditions must be met:

Role	Required permission
View creator (User A)	SELECT on the base table
View consumer (User B)	SELECT on the view

User B does not need direct access to the base table — only to the view. The view creator's permissions are used when the view is executed.

Syntax

CREATE VIEW <view_name> AS
SELECT column1, column2, ...
FROM table_name
WHERE [condition];

<view_name> is the name of the view. The SELECT statement defines the query that the view represents.

Examples

Create a view based on an internal table

Create an internal table and insert sample data:

CREATE TABLE holo_test (
  amount decimal(10, 2),
  rate   decimal(10, 2)
);

INSERT INTO holo_test VALUES
  (12.12, 13.13),
  (14.14, 15.15),
  (16.16, 17.17),
  (17.1,  17),
  (18.01, 19);

Create a view on the table and query it:

CREATE VIEW holo_view AS SELECT * FROM holo_test;

SELECT * FROM holo_view;

Expected output:

 amount | rate
--------+-------
  12.12 | 13.13
  14.14 | 15.15
  16.16 | 17.17
  17.10 | 17.00
  18.01 | 19.00
(5 rows)

Create a view based on a foreign table

Create a foreign table that maps to a MaxCompute (ODPS) table:

CREATE FOREIGN TABLE IF NOT EXISTS holo_foreign_test (
  amount decimal(10, 2),
  rate   decimal(10, 2)
)
SERVER odps_server
OPTIONS (project_name '<projectname>', table_name '<odps_name>');

SELECT * FROM holo_foreign_test LIMIT 2;

Create a view on the foreign table and query it:

CREATE VIEW foreign_view AS SELECT * FROM holo_foreign_test;

SELECT * FROM foreign_view LIMIT 2;

Expected output:

 amount | rate
--------+-------
  12.12 | 13.13
  14.14 | 15.15

Create a federated view

A federated view unifies data from internal tables and foreign tables into a single queryable object. This is useful when part of your data lives in Hologres internal storage and the rest resides in an external source such as MaxCompute — you can query both through one view without moving data. The federated view uses UNION ALL to combine the two sources.

CREATE VIEW view1 AS
  SELECT * FROM holo_view
  UNION ALL
  SELECT * FROM foreign_view;

SELECT * FROM view1;

Expected output:

 amount | rate
--------+-------
  12.12 | 13.13
  14.14 | 15.15
  16.16 | 17.17
   17.1 |    17
  18.01 |    19
  12.12 | 13.13
  14.14 | 15.15
  16.16 | 17.17
  17.10 | 17.00
  18.01 | 19.00
  12.12 | 13.13
  14.14 | 15.15
  16.16 | 17.17
   17.1 |    17
  18.01 |    19
  12.12 | 13.13
  14.14 | 15.15
  16.16 | 17.17
   17.1 |    17
  18.01 |    19
(20 rows)

More operations

Rename a view

Renaming views requires Hologres V2.1.18 or later.

ALTER VIEW <view_name_1> RENAME TO <view_name_2>;

Drop a view

DROP VIEW <view_name>;

List all views

Run the following SQL to list all views in the current schema:

SELECT n.nspname AS "Schema",
       c.relname AS "Name",
       CASE c.relkind
         WHEN 'r' THEN 'table'
         WHEN 'v' THEN 'view'
         WHEN 'm' THEN 'materialized view'
         WHEN 'i' THEN 'index'
         WHEN 'S' THEN 'sequence'
         WHEN 's' THEN 'special'
         WHEN 'f' THEN 'foreign table'
         WHEN 'p' THEN 'table'
         WHEN 'I' THEN 'index'
       END AS "Type",
       pg_catalog.pg_get_userbyid(c.relowner) AS "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v', '')
  AND n.nspname <> 'pg_catalog'
  AND n.nspname <> 'information_schema'
  AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;

If you are using a psql client, run \dv instead.

Query the DDL of a view

CREATE EXTENSION hg_toolkit;
SELECT hg_dump_script('<view_name>');