Skip to main content Link Menu Expand (external link) Document Search Copy Copied

CREATE VIEW statement

Creates a FeatureBase view. The view already exists and IF NOT EXISTS is not specified the statement will not be successful.

BNF diagrams

expr

Syntax 

CREATE VIEW view_name
  [IF NOT EXISTS]
  AS
  select_statement;

Arguments

Argument Description Required? Additional information
view_name Valid view name Yes Naming standards
IF NOT EXISTS Optional argument that stops statement execution if a view of the same name already exists No  
select_statement Valid select statement. Yes SELECT

Additional information

  • Run SELECT * FROM fb_views to output SELECT statements used to populate a view.

Naming standards

FeatureBase identifiers (including object names such as databases, tables and columns) start with a lower-case alphabetic character and can include:

  • lower-case alphabetic characters
  • numbers 0-9

  • dash - and underscore _ characters.

  • table names can be up to 230 characters in length

  • column names can be up to 64 characters in length

Examples

Create VIEW to implement business logic

In this example the retiree view implements the business rule identifying the retirees. The view also has a new column retirement_dt, which is a virtual value derived from the physical column dob.

create table person (_id id, dob timestamp);

insert into person(_id, dob)
values (1, '1950-01-01T00:00:00Z');
insert into person(_id, dob)
values (2, '1960-01-01T00:00:00Z');
insert into person(_id, dob)
values (3, '1970-01-01T00:00:00Z');
insert into person(_id, dob)
values (4, '2080-01-01T00:00:00Z');

create view retiree as
select _id, datetimeadd('yy',62,dob) retirement_dt
from person
where datetimeadd('yy',62,dob)>current_timestamp;

select * from retiree order by retirement_dt;

 _id | retirement_dt
-----+----------------------
   1 | 2012-01-01T00:00:00Z
   2 | 2022-01-01T00:00:00Z

CREATE VIEW to control data access

In this example the customer view is defined to show only data that is safe to share with all users. An underprivileged user can be given access to the customer view rather than the person table.

create table person (_id id, dob timestamp, ssn string, name string);

insert into person(_id, dob, ssn, name)
values (1, '1950-01-01T00:00:00Z', '123456789', 'John Doe 1');
insert into person(_id, dob, ssn, name)
values (2, '1960-01-01T00:00:00Z', '123456780', 'John Doe 2');
insert into person(_id, dob, ssn, name)
values (3, '1970-01-01T00:00:00Z', '123456700', 'John Doe 3');
insert into person(_id, dob, ssn, name)
values (4, '2080-01-01T00:00:00Z', '123456000', 'John Doe 4');

create view customer as
select _id, name, datetimepart('yy',dob) birth_year
from person;

select * from customer order by birth_year;

 _id | name       |      birth_year
-----+------------+-----------------
   1 | John Doe 1 |            1950
   2 | John Doe 2 |            1960
   3 | John Doe 3 |            1970
   4 | John Doe 4 |            2080

Further information