Squeal.PostgreSQL.Definition.View

create and drop views

Create

createView

Arguments

:: (Has sch db schema, KnownSymbol vw)

=> QualifiedAlias sch vw

the name of the view to add

-> Query '[] '[] db '[] view

query

-> Definition db (Alter sch (Create vw ('View view) schema) db)

Create a view.

>>> type ABC = '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4]
>>> type BC = '["b" ::: 'Null 'PGint4, "c" ::: 'Null 'PGint4]
>>> :{
let
  definition :: Definition
    '[ "public" ::: '["abc" ::: 'Table ('[] :=> ABC)]]
    '[ "public" ::: '["abc" ::: 'Table ('[] :=> ABC), "bc"  ::: 'View BC]]
  definition =
    createView #bc (select_ (#b :* #c) (from (table #abc)))
in printSQL definition
:}
CREATE VIEW "bc" AS SELECT "b" AS "b", "c" AS "c" FROM "abc" AS "abc";

createOrReplaceView

Arguments

:: (Has sch db schema, KnownSymbol vw)

=> QualifiedAlias sch vw

the name of the view to add

-> Query '[] '[] db '[] view

query

-> Definition db (Alter sch (CreateOrReplace vw ('View view) schema) db)

Create or replace a view.

>>> type ABC = '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4]
>>> type BC = '["b" ::: 'Null 'PGint4, "c" ::: 'Null 'PGint4]
>>> :{
let
  definition :: Definition
    '[ "public" ::: '["abc" ::: 'Table ('[] :=> ABC)]]
    '[ "public" ::: '["abc" ::: 'Table ('[] :=> ABC), "bc"  ::: 'View BC]]
  definition =
    createOrReplaceView #bc (select_ (#b :* #c) (from (table #abc)))
in printSQL definition
:}
CREATE OR REPLACE VIEW "bc" AS SELECT "b" AS "b", "c" AS "c" FROM "abc" AS "abc";

Drop

dropView

Arguments

:: (Has sch db schema, KnownSymbol vw)

=> QualifiedAlias sch vw

view to remove

-> Definition db (Alter sch (DropSchemum vw 'View schema) db)

Drop a view.

>>> :{
let
  definition :: Definition
    '[ "public" ::: '["abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])
     , "bc"  ::: 'View ('["b" ::: 'Null 'PGint4, "c" ::: 'Null 'PGint4])]]
    '[ "public" ::: '["abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])]]
  definition = dropView #bc
in printSQL definition
:}
DROP VIEW "bc";

dropViewIfExists

Arguments

:: (Has sch db schema, KnownSymbol vw)

=> QualifiedAlias sch vw

view to remove

-> Definition db (Alter sch (DropIfExists vw schema) db)

Drop a view if it exists.

>>> :{
let
  definition :: Definition
    '[ "public" ::: '["abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])
     , "bc"  ::: 'View ('["b" ::: 'Null 'PGint4, "c" ::: 'Null 'PGint4])]]
    '[ "public" ::: '["abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])]]
  definition = dropViewIfExists #bc
in printSQL definition
:}
DROP VIEW IF EXISTS "bc";

Alter

alterViewRename

Arguments

:: (Has sch db schema, KnownSymbol ty1, Has ty0 schema ('View vw))

=> QualifiedAlias sch ty0

view to rename

-> Alias ty1

what to rename it

-> Definition db (Alter sch (Rename ty0 ty1 schema) db)

alterViewRename changes the name of a view from the schema.

>>> type DB = '[ "public" ::: '[ "foo" ::: 'View '[] ] ]
>>> :{
 let def :: Definition DB '["public" ::: '["bar" ::: 'View '[] ] ]
     def = alterViewRename #foo #bar
 in printSQL def
:}
ALTER VIEW "foo" RENAME TO "bar";

alterViewSetSchema

Arguments

:: (Has sch0 db schema0, Has vw schema0 ('View view), Has sch1 db schema1)

=> QualifiedAlias sch0 vw

view to move

-> Alias sch1

where to move it

-> Definition db (SetSchema sch0 sch1 schema0 schema1 vw 'View view db)

This form moves the view into another schema.

>>> type DB0 = '[ "sch0" ::: '[ "vw" ::: 'View '[] ], "sch1" ::: '[] ]
>>> type DB1 = '[ "sch0" ::: '[], "sch1" ::: '[ "vw" ::: 'View '[] ] ]
>>> :{
let def :: Definition DB0 DB1
    def = alterViewSetSchema (#sch0 ! #vw) #sch1
in printSQL def
:}
ALTER VIEW "sch0"."vw" SET SCHEMA "sch1";