S-SQL Examples E

S-SQL Examples Home Page

A B C D E F G H I J K L M N O P R S T U V W Special Characters Calling Postgresql Stored Functions and Procedures

Enum

Per the postgresql documentation, Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages. An example of an enum type might be the days of the week, or a set of status values for a piece of data. In this example, we are going to create an enumerated type "rainbow" with specific allowed colors. 

(query (:create-enum 'rainbow
                     (list "red" "orange" "yellow" "green" "blue" "purple")))

(query (:create-table test26
                      ((name :type text)
                       (color :type rainbow))))

(query (:insert-into 'test26 :set 'name "Moe" 'current-color "yellow"))

Now if we try to insert an invalid type, we will trigger an error: 

(query (:insert-into 'test26 :set 'name "Moe" 'current-color "chartreuse"))

Database error 22P02: invalid input value for enum rainbow: "chartreuse" Query: insert into test26 values ('Moe', 'chartreuse')

[Condition of type CL-POSTGRES-ERROR:DATA-EXCEPTION]

Except

The sql except operator returns rows that are in the first selection but not in the second selection. Following on with our like example, assume we want all countries with "New" in their name, but not countries with "Zealand" in their names. 

(query (:except (:select 'id 'name
                         :from 'countries
                         :where (:like 'name "%New%"))
                (:select 'id 'name
                         :from 'countries
                         :where (:like 'name "%Zealand%"))))

((108 "Papua New Guinea") (103 "New Caledonia"))

Exists

Exists is used to run one subquery when a second subquery returns at least one row. For example: 

  (query (:select 'id 'name
                :from 'regions
                :where (:exists
                        (:select 'region-id
                                 :from 'countries
                                 :where (:and
                                         (:= 'countries.name "Costa Rica")
                                         (:= 'regions.id 'countries.region-id))))))

((3 "Central America"))

Extract

(query (:order-by (:select 'facid
                         (:as (:extract 'month 'starttime) 'month)
                         (:as (:sum 'slots) 'total-slots)
                         :from 'cd.bookings
                         :where (:and (:>= 'starttime "2012-01-01")
                                      (:< 'starttime "2013-01-01"))
                         :group-by 'facid 'month)
                'facid 'month))