Session 7 : views, functions

Last time

We saw

• B-tree indexes
  • Tree structure
  • Rules to lower overhead
• Hash indexes
  • Hash functions
• when to use a B-tree or Hash Index :
  • data not entirely random,
  • data not too simple (boolean for instance)
  • operators = (for Hash) or comparison for B-tree
  • used depending on the volume of data returned

Today

PostgreSQL is not only about tables structure and writing queries but it implements other elements to extend application possibilities.

• views : on the fly (in memory or materialized) queries as tables.
• functions : writing functions in PL/pgSQL
• triggers : modification on a table automatically triggers an action
• stored procedures : special functions

We will mostly work on the normalized version of the treesdb on your local.

Practice

• write a function that assigns a Hash to each record
• write a function that complements the data with calls to an external API

EOD

You will be able to

• create views
• write PL/pgSQL functions