Views

• A view in SQL is essentially a saved query

• Existing views can be treated just like tables, selecting from their contents

  • You can even, to a limited extent, add, remove, or update contents

• Syntax similar to creating a new table, just with VIEW

  CREATE VIEW |||view name||| AS
  SELECT |||columns|||
  FROM |||table|||

View vs Table

• Despite seeming similar, views are fundamentally different from tables
  • A view stores no new internal information in the database, whereas creating a new table copies that information
    • This means a view will always reflect the latest data, whereas a created table would need to be updated
  • Views can give users access only to particular columns
    • This can be a boon for security and giving only those who need them permissions to change various tables

View Operations

• You can drop views just like tables: DROP VIEW |||view name|||;
• You can create or replace views if the original columns and corresponding types are the same:
  • CREATE OR REPLACE VIEW |||view name||| ...
  • Avoids having to drop first to recreate
  • New view could have more columns, but can not have less
• Insertions, updates and removes can be made on simple views, which will propagate back to the original table!
  • Views must reference single tables (no joins) and have no distinct or group by type clauses
  • Data in the original table not present in the view will be given NULL values (if not further restrictions on the view)

Further Limiting Views

• Can also add WITH LOCAL CHECK OPTION to the end of a view creation query
• This will enforce that only data visible within the view can be edited with inserts, updates or removes
  • This includes any filtering done by the view!
• Can be an excellent way to limit what data can be changed
• Can replace LOCAL with CASCADED if you are referencing a view nested several times and want the restrictions to apply based on all the parent views as well
  • Needs to be applied at every nesting level

Functions

• Postgres has many prebuilt functions that we have used over the semester
• Sometimes you need something a bit more bespoke though, for which Postgres gives you some “easy” methods of writing your own functions
• What language you use to write your own function is highly flexible:
  • Pure SQL
  • PL/pgSQL: an extension of SQL that offers more programming logic
  • Python
• All functions will follow a similar syntax structure, but the body will depend on the details of your chosen language
  • Documentation for all the languages (and functions in general) can be found here

Structure of Function

• The structure of any defined function will generally look like:

  CREATE FUNCTION |||func_name|||(|||func_arguments|||)
  RETURNS |||return_type||| AS
  |||'function_body'|||
  LANGUAGE |||func_lang|||;

• func_name - The desired name of the function

• func_arguments - A comma separated list of paired arguments and types

• return_type - The variable type of what will be returned (selected)

• func_lang - The language used for the function body

• function_body - a string containing the query or logic to process whenever the function is run

In SQL

• LANGUAGE SQL
• Using the basic SQL language gives you access to any existing SQL queries that you already know
• To return something you should use the SELECT
• You can also write functions that do not return something by specifying RETURNS void and then using INSERT, UPDATE, DELETE, etc.
• Using $$ to quote the body is recommended so that you do not need to double up any single quotes

In pgSQL

• LANGUAGE plpgsql
• Extends SQL to add classic programming control structures
• Needs to be wrapped in BEGIN, END keywords
• Operates like a hybrid between SQL and a more typical programming language
• Each phrase needs a terminating semicolon at the end

pgSQL Structures

• You can define variables

  x := 56;

• Control statements

  IF x > 5 THEN
    |||...do something...|||
  ELSE
    |||...do something else...|||
  END IF;

• Returns

  RETURN x;

In Python

• LANGUAGE plpython3u

• Python is another supported language in which functions can be written

• Requires an extension to be active:

  CREATE EXTENSION plpython3u;

• Most common data types with corresponding types are converted, anything else (like timestamps!) are just utilized as strings, so plan accordingly

• I’d generally write the Python in a more Python suited editor and then copy it in

Activity!

Group Work!

• The remainder of class is set aside for you to work with your group on your projects!