Structured Types in PostgreSQL

1
Structured TypesinPostgreSQL

• Lecturer Dr Pavle Mogin

2
Plan for Structured Types in PostgreSQL

• Structured Types in PostgreSQL
• ROW,
• ARRAY
• ROW types
• Declaring,
• Updating, and
• Querying
• ARRAY
• One dimensional
• Multidemensional
• Declaring,
• Updating, and
• Querying
• Reading
• PostgreSQL Manual

3
Composite Types

• A composite or row type is a list of (attribute,
  base_type) pairs
• A value of a composite type is a row of values (a
  tuple)
• In PostgreSQL a composite type
• Is created whenever a user creates a table, but
• Can also be declared using CREATE TYPE AS command
  
• Both can be further assigned to a table column as
  a type
• So, table columns can hold tuples

4
Declaring Composite Types

• We define any of these two
• CREATE TYPE customer AS (
• CustomerId int, --No constraints
• Cust_Name varchar, --can be specified
• City varchar) --for a ROW type
• CREATE TABLE customer (
• CustomerId int PRIMARY KEY,
• Cust_Name varchar NOT NUL,
• City varchar
• CHECK (City IN (Wellington,Upper Hutt,Lower
  Hutt)))
• and use one of them in
• CREATE TABLE IvoiceHeader(
• InvoiceNo int PRIMARY KEY,
• Customer customer-- No constraints either
• Total decimal (8,2))

5
Inserting, Modifying and Querying

• INSERT INTO InvoiceHeader VALUES
• (1234, ROW(007,James, Upper Hutt)
• UPDATE InvoiceHeader SET
• (Customer).City Porirua WHERE
  (Customer).Customerid 007
• --Mind parenthesis
• SELECT (Customer).Cust_Name
• FROM InvoiceHEADER
• WHERE InvoiceNo 1234

6
Row-wise Comparison

• Row-wise comparison is done using operators ,
  ltgt, lt, lt, gt, gt
• The syntax
• row_constructor operator row_constructor
• The two values have to have the same number of
  fields
• The operator returns true if the corresponding
  fields are not null and equal
• For the operators lt, lt, gt, and gt fields are
  compared from left to right, stopping as soon as
  an unequal or null pair is found

7
Two Questions for You

• Why do we need to compare whole row types? Isnt
  it enough to compare only primary key values?
• What will comparison of the following two row
  type values
• (COMP442, 2007, C) lt (COMP442, 2008, A)
• return?
• TRUE
• FALSE
• NULL

8
Arrays in PostgreSQL

• Of all collection types, SQL1999 supports only
  arrays
• Arrays are the only collection type constructor
  supported by PostgreSQL
• An array itself is not a data type, but a
  constructor to build new data types of any
  PostgreSQL data type
• PostgreSQL supports single and multidimensional
  arrays of variable length
• A multivalued attribute A is declared in a CREATE
  TABLE or ALTER TABLE statement by appending
  pairs of brackets to its data type one pair for
  each dimension

9
Declaring Arrays

• CREATE TABLE Class (
• CourseId char(7),
• Year smallint,
• Students text)
• CREATE TABLE Exam (
• StudId int,
• Stud_Name char (10),
• Courses_and_Grades text)

10
Principles of Inserting Values

• All elements of an array are inserted using one
  SQL INSERT statement
• An entry in an array (within an INSER statement)
  is placed
• In braces ,
• Braces are located between single quotes ,
• In an one dimensional array, elements are comma
  separated e1,, en
• Character strings (text) elements are placed in
  double quotation marks e1,, en, but
  numeric types are not
• All elements of an array of a multi dimensional
  array are placed within their own pair of braces
• e11, e12,, e1n, e21, e22,, e2n,, en1,
  en2,, enn

11
Inserting Values Into Arrays

• INSERT INTO Class VALUES (COMP302, 2001,
  Ahmed, Craig, Charlie, Daniel, Dough,
  Frank, Nick, Shusen)
• INSERT INTO Class VALUES (COMP442, 2001,
  Jason, Matthew, Paul, Dough, Hans,
  Valeriy, Yingjie)

12
Inserting Values Into Multi Arrays

• INSERT INTO Exam VALUES
• (7007, Ahmed, COMP302, A)
• INSERT INTO Exam VALUES (5555, Craig,
  COMP203, COMP302, COMP305,
• A-, A, B)

13
Retrieving Array Elements

• The elements of an array may be retrieved
• As a whole,
• Using subscripts, and
• Using array slices
• SELECT Students FROM Class
• WHERE CourseId COMP302
• ----------------------------------------
• Ahmed, Craig, Charlie, Daniel, Dough,
  Frank, Nick, Shusen
• (1 rows)

14
Use of Single Subscripts

• PostgreSQL begins counting array elements at 1
• To retrieve an element from an array, the exact
• subscript of that element has to be known
• Find the second student in COMP302 class
• SELECT Students2 FROM Class
• WHERE CourseID COMP302
• ---------
• Craig
• (1 row)

15
Use of Double Subscripts

• Selecting data from a two dimensional array needs
  two
• subscripts
• The first designates the array, and
• The second refers to the element
• Find the second course and grade of student Craig
• SELECT Courses_and_Grades12 AS Course,
• Courses_and_Grades22 AS Grade
• FROM Exam
• WHERE Stud_Name Craig
• course grade
• ----------------
• COMP302 A
• (1 row)

16
Array Slices

• A slice ltattribute_namegtjk is used in queries
  in a similar manner as subscripts
• The slice jk designates that all array
  elements from j to k should be retrieved
• Retrieve COMP302 students four, five, and six
  from class
• SELECT Students46 FROM Class
• WHERE CourseID COMP302
• ---------
• Daniel, Dough, Frank
• (1 row)

17
Updating an Array

• An array may be updated in a variety of ways
• Modifying value of an array element,
• Extending a one dimensional array, and
• Replacing the whole array with a new one (the new
  one can have different number of elements)
• UPDATE Exam SET Courses_and_Grades21 A
• WHERE Stud_Name Craig
• UPDATE Class SET Students9 Ivana
• WHERE Courseid COMP302
• UPDATE Exam SET Courses_and_Grades
• COMP302, COMP442, A, B
• WHERE Stud_Name Ahmed

18
Concatenating Arrays

• Single dimensional arrays can be extended using
  concatenation operator
• update class set studetsstudets
  ARRAY'Milan', 'Daniela' where
  courseid'COMP302'
• UPDATE 1
• Operator can be used to add extra dimensions
  to a multidimensional array, but not to extend it

19
Array Comparison

• If an array is large, to check for existence of a
  given value in the array we use the comparator
  ANY (SOME)
• SELECT CourseId, year
• FROM Class
• WHERE Craig ANY (Students)
• course year
• ----------------
• COMP302 2001
• (1 row)

20
PostgreSQL Arrays a Critique

• The current version of PostgreSQL does not offer
  great comfort in manipulating arrays
• Deleting an arbitrary array element is not
  supported,
• Inserting an array element in an arbitrary
  position is not supported,
• Extending an existing multidimensional array by
  new elements doesnt seem to be supported
• The operators to perform multiset related
  operations are not supported (e.g. intersect)

21
Summary

• PostgreSQL supports the following structured
  types
• Row (composite), and
• Array (single and multidimensional)
• A row type can be declared either using a CREATE
  TYPEAS or CREATE TABLE command
• A table column can be assigned a row type
• Arrays are defined on base types solely
• A table column can be assigned an array
• Both structured types can be
• Inserted,
• Modifies, and
• Queried

22
Plan for the next topic

• Object identifier in SQL19999 standard
• Object identifier in PostgreSQL
• Making oids visible
• Defining columns of oid type
• Reference type
• Dereferencing reference types
• Querying tables with reference types
• Reference types versus structured types