summaryrefslogtreecommitdiffstats
path: root/src/tutorial
diff options
context:
space:
mode:
Diffstat (limited to 'src/tutorial')
-rw-r--r--src/tutorial/.gitignore5
-rw-r--r--src/tutorial/Makefile33
-rw-r--r--src/tutorial/README16
-rw-r--r--src/tutorial/advanced.source60
-rw-r--r--src/tutorial/basics.source215
-rw-r--r--src/tutorial/complex.c209
-rw-r--r--src/tutorial/complex.source228
-rw-r--r--src/tutorial/funcs.c126
-rw-r--r--src/tutorial/funcs.source160
-rw-r--r--src/tutorial/syscat.source175
10 files changed, 1227 insertions, 0 deletions
diff --git a/src/tutorial/.gitignore b/src/tutorial/.gitignore
new file mode 100644
index 0000000..304f9df
--- /dev/null
+++ b/src/tutorial/.gitignore
@@ -0,0 +1,5 @@
+/advanced.sql
+/basics.sql
+/complex.sql
+/funcs.sql
+/syscat.sql
diff --git a/src/tutorial/Makefile b/src/tutorial/Makefile
new file mode 100644
index 0000000..16dc390
--- /dev/null
+++ b/src/tutorial/Makefile
@@ -0,0 +1,33 @@
+#-------------------------------------------------------------------------
+#
+# Makefile--
+# Makefile for tutorial
+#
+# By default, this builds against an existing PostgreSQL installation
+# (the one identified by whichever pg_config is first in your path).
+# Within a configured source tree, you can say "make NO_PGXS=1 all"
+# to build using the surrounding source tree.
+#
+# IDENTIFICATION
+# src/tutorial/Makefile
+#
+#-------------------------------------------------------------------------
+
+MODULES = complex funcs
+DATA_built = advanced.sql basics.sql complex.sql funcs.sql syscat.sql
+
+ifdef NO_PGXS
+subdir = src/tutorial
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/src/makefiles/pgxs.mk
+else
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+endif
+
+%.sql: %.source
+ rm -f $@; \
+ C=`pwd`; \
+ sed -e "s:_OBJWD_:$$C:g" < $< > $@
diff --git a/src/tutorial/README b/src/tutorial/README
new file mode 100644
index 0000000..b137cdf
--- /dev/null
+++ b/src/tutorial/README
@@ -0,0 +1,16 @@
+src/tutorial/README
+
+tutorial
+========
+
+This directory contains SQL tutorial scripts. To look at them, first do a
+ % make
+to compile all the scripts and C files for the user-defined functions
+and types. (make needs to be GNU make --- it may be named something
+different on your system, often 'gmake')
+
+Then, run psql with the -s (single-step) flag:
+ % psql -s
+
+From within psql, you can try each individual script file by using
+psql's \i <filename> command.
diff --git a/src/tutorial/advanced.source b/src/tutorial/advanced.source
new file mode 100644
index 0000000..0c68b33
--- /dev/null
+++ b/src/tutorial/advanced.source
@@ -0,0 +1,60 @@
+---------------------------------------------------------------------------
+--
+-- advanced.sql-
+-- Tutorial on advanced PostgreSQL features
+--
+--
+-- Copyright (c) 1994, Regents of the University of California
+--
+-- src/tutorial/advanced.source
+--
+---------------------------------------------------------------------------
+
+-----------------------------
+-- Inheritance:
+-- A table can inherit from zero or more tables. A query can reference
+-- either all rows of a table or all rows of a table plus all of its
+-- descendants.
+-----------------------------
+
+-- For example, the capitals table inherits from cities table. (It inherits
+-- all data fields from cities.)
+
+CREATE TABLE cities (
+ name text,
+ population float8,
+ elevation int -- (in ft)
+);
+
+CREATE TABLE capitals (
+ state char(2)
+) INHERITS (cities);
+
+-- Now, let's populate the tables.
+INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63);
+INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174);
+INSERT INTO cities VALUES ('Mariposa', 1200, 1953);
+
+INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA');
+INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI');
+
+SELECT * FROM cities;
+SELECT * FROM capitals;
+
+-- You can find all cities, including capitals, that
+-- are located at an elevation of 500 ft or higher by:
+
+SELECT c.name, c.elevation
+FROM cities c
+WHERE c.elevation > 500;
+
+-- To scan rows of the parent table only, use ONLY:
+
+SELECT name, elevation
+FROM ONLY cities
+WHERE elevation > 500;
+
+
+-- clean up (you must remove the children first)
+DROP TABLE capitals;
+DROP TABLE cities;
diff --git a/src/tutorial/basics.source b/src/tutorial/basics.source
new file mode 100644
index 0000000..d09ff50
--- /dev/null
+++ b/src/tutorial/basics.source
@@ -0,0 +1,215 @@
+---------------------------------------------------------------------------
+--
+-- basics.sql-
+-- Tutorial on the basics (table creation and data manipulation)
+--
+--
+-- src/tutorial/basics.source
+--
+---------------------------------------------------------------------------
+
+-----------------------------
+-- Creating a New Table:
+-- A CREATE TABLE is used to create base tables. PostgreSQL has
+-- its own set of built-in types. (Note that SQL is case-
+-- insensitive.)
+-----------------------------
+
+CREATE TABLE weather (
+ city varchar(80),
+ temp_lo int, -- low temperature
+ temp_hi int, -- high temperature
+ prcp real, -- precipitation
+ date date
+);
+
+CREATE TABLE cities (
+ name varchar(80),
+ location point
+);
+
+
+-----------------------------
+-- Populating a Table With Rows:
+-- An INSERT statement is used to insert a new row into a table. There
+-- are several ways you can specify what columns the data should go to.
+-----------------------------
+
+-- 1. The simplest case is when the list of value correspond to the order of
+-- the columns specified in CREATE TABLE.
+
+INSERT INTO weather
+ VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
+
+INSERT INTO cities
+ VALUES ('San Francisco', '(-194.0, 53.0)');
+
+-- 2. You can also specify what column the values correspond to. (The columns
+-- can be specified in any order. You may also omit any number of columns,
+-- e.g., unknown precipitation below.
+
+INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
+ VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
+
+INSERT INTO weather (date, city, temp_hi, temp_lo)
+ VALUES ('1994-11-29', 'Hayward', 54, 37);
+
+
+-----------------------------
+-- Querying a Table:
+-- A SELECT statement is used for retrieving data. The basic syntax is
+-- SELECT columns FROM tables WHERE predicates.
+-----------------------------
+
+-- A simple one would be:
+
+SELECT * FROM weather;
+
+-- You may also specify expressions in the target list. (The 'AS column'
+-- specifies the column name of the result. It is optional.)
+
+SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
+
+-- If you want to retrieve rows that satisfy certain condition (i.e., a
+-- restriction), specify the condition in WHERE. The following retrieves
+-- the weather of San Francisco on rainy days.
+
+SELECT *
+ FROM weather
+ WHERE city = 'San Francisco'
+ AND prcp > 0.0;
+
+-- You can request that the results of a query be returned in sorted order:
+
+SELECT * FROM weather
+ ORDER BY city, temp_lo;
+
+-- Here is a more complicated one. Duplicates are removed when DISTINCT is
+-- specified. ORDER BY specifies the column to sort on. (Just to make sure the
+-- following won't confuse you, DISTINCT and ORDER BY can be used separately.)
+
+SELECT DISTINCT city
+ FROM weather
+ ORDER BY city;
+
+
+-----------------------------
+-- Joins Between Tables:
+-- queries can access multiple tables at once or access the same table
+-- in such a way that multiple instances of the table are being processed
+-- at the same time.
+-----------------------------
+
+-- The following joins the weather table and the cities table.
+
+SELECT * FROM weather JOIN cities ON city = name;
+
+-- This prevents a duplicate city name column:
+
+SELECT city, temp_lo, temp_hi, prcp, date, location
+ FROM weather JOIN cities ON city = name;
+
+-- since the column names are all different, we don't have to specify the
+-- table name. If you want to be clear, you can do the following. They give
+-- identical results, of course.
+
+SELECT weather.city, weather.temp_lo, weather.temp_hi,
+ weather.prcp, weather.date, cities.location
+ FROM weather JOIN cities ON weather.city = cities.name;
+
+-- Old join syntax
+
+SELECT *
+ FROM weather, cities
+ WHERE city = name;
+
+-- Outer join
+
+SELECT *
+ FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;
+
+-- Suppose we want to find all the records that are in the temperature range
+-- of other records. w1 and w2 are aliases for weather.
+
+SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,
+ w2.city, w2.temp_lo AS low, w2.temp_hi AS high
+FROM weather w1 JOIN weather w2
+ ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;
+
+
+-----------------------------
+-- Aggregate Functions
+-----------------------------
+
+SELECT max(temp_lo)
+ FROM weather;
+
+SELECT city FROM weather
+ WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
+
+-- Aggregate with GROUP BY
+SELECT city, count(*), max(temp_lo)
+ FROM weather
+ GROUP BY city;
+
+-- ... and HAVING
+SELECT city, count(*), max(temp_lo)
+ FROM weather
+ GROUP BY city
+ HAVING max(temp_lo) < 40;
+
+-- We can filter rows before aggregating them:
+SELECT city, count(*), max(temp_lo)
+ FROM weather
+ WHERE city LIKE 'S%'
+ GROUP BY city;
+
+-- Another way is the FILTER clause, which operates per-aggregate:
+SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)
+ FROM weather
+ GROUP BY city;
+
+
+-----------------------------
+-- Updates:
+-- An UPDATE statement is used for updating data.
+-----------------------------
+
+-- Suppose you discover the temperature readings are all off by 2 degrees as
+-- of Nov 28, you may update the data as follow:
+
+UPDATE weather
+ SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
+ WHERE date > '1994-11-28';
+
+SELECT * FROM weather;
+
+
+-----------------------------
+-- Deletions:
+-- A DELETE statement is used for deleting rows from a table.
+-----------------------------
+
+-- Suppose you are no longer interested in the weather of Hayward, then you can
+-- do the following to delete those rows from the table.
+
+DELETE FROM weather WHERE city = 'Hayward';
+
+SELECT * FROM weather;
+
+-- You can also delete all the rows in a table by doing the following. (This
+-- is different from DROP TABLE which removes the table in addition to the
+-- removing the rows.)
+
+DELETE FROM weather;
+
+SELECT * FROM weather;
+
+
+-----------------------------
+-- Removing the tables:
+-- DROP TABLE is used to remove tables. After you have done this, you
+-- can no longer use those tables.
+-----------------------------
+
+DROP TABLE weather, cities;
diff --git a/src/tutorial/complex.c b/src/tutorial/complex.c
new file mode 100644
index 0000000..6798a9e
--- /dev/null
+++ b/src/tutorial/complex.c
@@ -0,0 +1,209 @@
+/*
+ * src/tutorial/complex.c
+ *
+ ******************************************************************************
+ This file contains routines that can be bound to a Postgres backend and
+ called by the backend in the process of processing queries. The calling
+ format for these routines is dictated by Postgres architecture.
+******************************************************************************/
+
+#include "postgres.h"
+
+#include "fmgr.h"
+#include "libpq/pqformat.h" /* needed for send/recv functions */
+
+PG_MODULE_MAGIC;
+
+typedef struct Complex
+{
+ double x;
+ double y;
+} Complex;
+
+
+/*****************************************************************************
+ * Input/Output functions
+ *****************************************************************************/
+
+PG_FUNCTION_INFO_V1(complex_in);
+
+Datum
+complex_in(PG_FUNCTION_ARGS)
+{
+ char *str = PG_GETARG_CSTRING(0);
+ double x,
+ y;
+ Complex *result;
+
+ if (sscanf(str, " ( %lf , %lf )", &x, &y) != 2)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg("invalid input syntax for type %s: \"%s\"",
+ "complex", str)));
+
+ result = (Complex *) palloc(sizeof(Complex));
+ result->x = x;
+ result->y = y;
+ PG_RETURN_POINTER(result);
+}
+
+PG_FUNCTION_INFO_V1(complex_out);
+
+Datum
+complex_out(PG_FUNCTION_ARGS)
+{
+ Complex *complex = (Complex *) PG_GETARG_POINTER(0);
+ char *result;
+
+ result = psprintf("(%g,%g)", complex->x, complex->y);
+ PG_RETURN_CSTRING(result);
+}
+
+/*****************************************************************************
+ * Binary Input/Output functions
+ *
+ * These are optional.
+ *****************************************************************************/
+
+PG_FUNCTION_INFO_V1(complex_recv);
+
+Datum
+complex_recv(PG_FUNCTION_ARGS)
+{
+ StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
+ Complex *result;
+
+ result = (Complex *) palloc(sizeof(Complex));
+ result->x = pq_getmsgfloat8(buf);
+ result->y = pq_getmsgfloat8(buf);
+ PG_RETURN_POINTER(result);
+}
+
+PG_FUNCTION_INFO_V1(complex_send);
+
+Datum
+complex_send(PG_FUNCTION_ARGS)
+{
+ Complex *complex = (Complex *) PG_GETARG_POINTER(0);
+ StringInfoData buf;
+
+ pq_begintypsend(&buf);
+ pq_sendfloat8(&buf, complex->x);
+ pq_sendfloat8(&buf, complex->y);
+ PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
+}
+
+/*****************************************************************************
+ * New Operators
+ *
+ * A practical Complex datatype would provide much more than this, of course.
+ *****************************************************************************/
+
+PG_FUNCTION_INFO_V1(complex_add);
+
+Datum
+complex_add(PG_FUNCTION_ARGS)
+{
+ Complex *a = (Complex *) PG_GETARG_POINTER(0);
+ Complex *b = (Complex *) PG_GETARG_POINTER(1);
+ Complex *result;
+
+ result = (Complex *) palloc(sizeof(Complex));
+ result->x = a->x + b->x;
+ result->y = a->y + b->y;
+ PG_RETURN_POINTER(result);
+}
+
+
+/*****************************************************************************
+ * Operator class for defining B-tree index
+ *
+ * It's essential that the comparison operators and support function for a
+ * B-tree index opclass always agree on the relative ordering of any two
+ * data values. Experience has shown that it's depressingly easy to write
+ * unintentionally inconsistent functions. One way to reduce the odds of
+ * making a mistake is to make all the functions simple wrappers around
+ * an internal three-way-comparison function, as we do here.
+ *****************************************************************************/
+
+#define Mag(c) ((c)->x*(c)->x + (c)->y*(c)->y)
+
+static int
+complex_abs_cmp_internal(Complex * a, Complex * b)
+{
+ double amag = Mag(a),
+ bmag = Mag(b);
+
+ if (amag < bmag)
+ return -1;
+ if (amag > bmag)
+ return 1;
+ return 0;
+}
+
+
+PG_FUNCTION_INFO_V1(complex_abs_lt);
+
+Datum
+complex_abs_lt(PG_FUNCTION_ARGS)
+{
+ Complex *a = (Complex *) PG_GETARG_POINTER(0);
+ Complex *b = (Complex *) PG_GETARG_POINTER(1);
+
+ PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) < 0);
+}
+
+PG_FUNCTION_INFO_V1(complex_abs_le);
+
+Datum
+complex_abs_le(PG_FUNCTION_ARGS)
+{
+ Complex *a = (Complex *) PG_GETARG_POINTER(0);
+ Complex *b = (Complex *) PG_GETARG_POINTER(1);
+
+ PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) <= 0);
+}
+
+PG_FUNCTION_INFO_V1(complex_abs_eq);
+
+Datum
+complex_abs_eq(PG_FUNCTION_ARGS)
+{
+ Complex *a = (Complex *) PG_GETARG_POINTER(0);
+ Complex *b = (Complex *) PG_GETARG_POINTER(1);
+
+ PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) == 0);
+}
+
+PG_FUNCTION_INFO_V1(complex_abs_ge);
+
+Datum
+complex_abs_ge(PG_FUNCTION_ARGS)
+{
+ Complex *a = (Complex *) PG_GETARG_POINTER(0);
+ Complex *b = (Complex *) PG_GETARG_POINTER(1);
+
+ PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) >= 0);
+}
+
+PG_FUNCTION_INFO_V1(complex_abs_gt);
+
+Datum
+complex_abs_gt(PG_FUNCTION_ARGS)
+{
+ Complex *a = (Complex *) PG_GETARG_POINTER(0);
+ Complex *b = (Complex *) PG_GETARG_POINTER(1);
+
+ PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) > 0);
+}
+
+PG_FUNCTION_INFO_V1(complex_abs_cmp);
+
+Datum
+complex_abs_cmp(PG_FUNCTION_ARGS)
+{
+ Complex *a = (Complex *) PG_GETARG_POINTER(0);
+ Complex *b = (Complex *) PG_GETARG_POINTER(1);
+
+ PG_RETURN_INT32(complex_abs_cmp_internal(a, b));
+}
diff --git a/src/tutorial/complex.source b/src/tutorial/complex.source
new file mode 100644
index 0000000..bab21af
--- /dev/null
+++ b/src/tutorial/complex.source
@@ -0,0 +1,228 @@
+---------------------------------------------------------------------------
+--
+-- complex.sql-
+-- This file shows how to create a new user-defined type and how to
+-- use this new type.
+--
+--
+-- Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group
+-- Portions Copyright (c) 1994, Regents of the University of California
+--
+-- src/tutorial/complex.source
+--
+---------------------------------------------------------------------------
+
+-----------------------------
+-- Creating a new type:
+-- We are going to create a new type called 'complex' which represents
+-- complex numbers.
+-- A user-defined type must have an input and an output function, and
+-- optionally can have binary input and output functions. All of these
+-- are usually user-defined C functions.
+-----------------------------
+
+-- Assume the user defined functions are in _OBJWD_/complex$DLSUFFIX
+-- (we do not want to assume this is in the dynamic loader search path).
+-- Look at $PWD/complex.c for the source. Note that we declare all of
+-- them as STRICT, so we do not need to cope with NULL inputs in the
+-- C code. We also mark them IMMUTABLE, since they always return the
+-- same outputs given the same inputs.
+
+-- the input function 'complex_in' takes a null-terminated string (the
+-- textual representation of the type) and turns it into the internal
+-- (in memory) representation. You will get a message telling you 'complex'
+-- does not exist yet but that's okay.
+
+CREATE FUNCTION complex_in(cstring)
+ RETURNS complex
+ AS '_OBJWD_/complex'
+ LANGUAGE C IMMUTABLE STRICT;
+
+-- the output function 'complex_out' takes the internal representation and
+-- converts it into the textual representation.
+
+CREATE FUNCTION complex_out(complex)
+ RETURNS cstring
+ AS '_OBJWD_/complex'
+ LANGUAGE C IMMUTABLE STRICT;
+
+-- the binary input function 'complex_recv' takes a StringInfo buffer
+-- and turns its contents into the internal representation.
+
+CREATE FUNCTION complex_recv(internal)
+ RETURNS complex
+ AS '_OBJWD_/complex'
+ LANGUAGE C IMMUTABLE STRICT;
+
+-- the binary output function 'complex_send' takes the internal representation
+-- and converts it into a (hopefully) platform-independent bytea string.
+
+CREATE FUNCTION complex_send(complex)
+ RETURNS bytea
+ AS '_OBJWD_/complex'
+ LANGUAGE C IMMUTABLE STRICT;
+
+
+-- now, we can create the type. The internallength specifies the size of the
+-- memory block required to hold the type (we need two 8-byte doubles).
+
+CREATE TYPE complex (
+ internallength = 16,
+ input = complex_in,
+ output = complex_out,
+ receive = complex_recv,
+ send = complex_send,
+ alignment = double
+);
+
+
+-----------------------------
+-- Using the new type:
+-- user-defined types can be used like ordinary built-in types.
+-----------------------------
+
+-- eg. we can use it in a table
+
+CREATE TABLE test_complex (
+ a complex,
+ b complex
+);
+
+-- data for user-defined types are just strings in the proper textual
+-- representation.
+
+INSERT INTO test_complex VALUES ('(1.0, 2.5)', '(4.2, 3.55 )');
+INSERT INTO test_complex VALUES ('(33.0, 51.4)', '(100.42, 93.55)');
+
+SELECT * FROM test_complex;
+
+-----------------------------
+-- Creating an operator for the new type:
+-- Let's define an add operator for complex types. Since POSTGRES
+-- supports function overloading, we'll use + as the add operator.
+-- (Operator names can be reused with different numbers and types of
+-- arguments.)
+-----------------------------
+
+-- first, define a function complex_add (also in complex.c)
+CREATE FUNCTION complex_add(complex, complex)
+ RETURNS complex
+ AS '_OBJWD_/complex'
+ LANGUAGE C IMMUTABLE STRICT;
+
+-- we can now define the operator. We show a binary operator here but you
+-- can also define a prefix operator by omitting the leftarg.
+CREATE OPERATOR + (
+ leftarg = complex,
+ rightarg = complex,
+ procedure = complex_add,
+ commutator = +
+);
+
+
+SELECT (a + b) AS c FROM test_complex;
+
+-- Occasionally, you may find it useful to cast the string to the desired
+-- type explicitly. :: denotes a type cast.
+
+SELECT a + '(1.0,1.0)'::complex AS aa,
+ b + '(1.0,1.0)'::complex AS bb
+ FROM test_complex;
+
+
+-----------------------------
+-- Creating aggregate functions
+-- you can also define aggregate functions. The syntax is somewhat
+-- cryptic but the idea is to express the aggregate in terms of state
+-- transition functions.
+-----------------------------
+
+CREATE AGGREGATE complex_sum (
+ sfunc = complex_add,
+ basetype = complex,
+ stype = complex,
+ initcond = '(0,0)'
+);
+
+SELECT complex_sum(a) FROM test_complex;
+
+
+-----------------------------
+-- Interfacing New Types with Indexes:
+-- We cannot define a secondary index (eg. a B-tree) over the new type
+-- yet. We need to create all the required operators and support
+-- functions, then we can make the operator class.
+-----------------------------
+
+-- first, define the required operators
+CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
+ AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
+CREATE FUNCTION complex_abs_le(complex, complex) RETURNS bool
+ AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
+CREATE FUNCTION complex_abs_eq(complex, complex) RETURNS bool
+ AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
+CREATE FUNCTION complex_abs_ge(complex, complex) RETURNS bool
+ AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
+CREATE FUNCTION complex_abs_gt(complex, complex) RETURNS bool
+ AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
+
+CREATE OPERATOR < (
+ leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
+ commutator = > , negator = >= ,
+ restrict = scalarltsel, join = scalarltjoinsel
+);
+CREATE OPERATOR <= (
+ leftarg = complex, rightarg = complex, procedure = complex_abs_le,
+ commutator = >= , negator = > ,
+ restrict = scalarlesel, join = scalarlejoinsel
+);
+CREATE OPERATOR = (
+ leftarg = complex, rightarg = complex, procedure = complex_abs_eq,
+ commutator = = ,
+ -- leave out negator since we didn't create <> operator
+ -- negator = <> ,
+ restrict = eqsel, join = eqjoinsel
+);
+CREATE OPERATOR >= (
+ leftarg = complex, rightarg = complex, procedure = complex_abs_ge,
+ commutator = <= , negator = < ,
+ restrict = scalargesel, join = scalargejoinsel
+);
+CREATE OPERATOR > (
+ leftarg = complex, rightarg = complex, procedure = complex_abs_gt,
+ commutator = < , negator = <= ,
+ restrict = scalargtsel, join = scalargtjoinsel
+);
+
+-- create the support function too
+CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4
+ AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
+
+-- now we can make the operator class
+CREATE OPERATOR CLASS complex_abs_ops
+ DEFAULT FOR TYPE complex USING btree AS
+ OPERATOR 1 < ,
+ OPERATOR 2 <= ,
+ OPERATOR 3 = ,
+ OPERATOR 4 >= ,
+ OPERATOR 5 > ,
+ FUNCTION 1 complex_abs_cmp(complex, complex);
+
+
+-- now, we can define a btree index on complex types. First, let's populate
+-- the table. Note that postgres needs many more tuples to start using the
+-- btree index during selects.
+INSERT INTO test_complex VALUES ('(56.0,-22.5)', '(-43.2,-0.07)');
+INSERT INTO test_complex VALUES ('(-91.9,33.6)', '(8.6,3.0)');
+
+CREATE INDEX test_cplx_ind ON test_complex
+ USING btree(a complex_abs_ops);
+
+SELECT * from test_complex where a = '(56.0,-22.5)';
+SELECT * from test_complex where a < '(56.0,-22.5)';
+SELECT * from test_complex where a > '(56.0,-22.5)';
+
+
+-- clean up the example
+DROP TABLE test_complex;
+DROP TYPE complex CASCADE;
diff --git a/src/tutorial/funcs.c b/src/tutorial/funcs.c
new file mode 100644
index 0000000..cdd155e
--- /dev/null
+++ b/src/tutorial/funcs.c
@@ -0,0 +1,126 @@
+/* src/tutorial/funcs.c */
+
+/******************************************************************************
+ These are user-defined functions that can be bound to a Postgres backend
+ and called by Postgres to execute SQL functions of the same name.
+
+ The calling format for these functions is defined by the CREATE FUNCTION
+ SQL statement that binds them to the backend.
+*****************************************************************************/
+
+#include "postgres.h" /* general Postgres declarations */
+
+#include "executor/executor.h" /* for GetAttributeByName() */
+#include "utils/geo_decls.h" /* for point type */
+
+PG_MODULE_MAGIC;
+
+
+/* By Value */
+
+PG_FUNCTION_INFO_V1(add_one);
+
+Datum
+add_one(PG_FUNCTION_ARGS)
+{
+ int32 arg = PG_GETARG_INT32(0);
+
+ PG_RETURN_INT32(arg + 1);
+}
+
+/* By Reference, Fixed Length */
+
+PG_FUNCTION_INFO_V1(add_one_float8);
+
+Datum
+add_one_float8(PG_FUNCTION_ARGS)
+{
+ /* The macros for FLOAT8 hide its pass-by-reference nature */
+ float8 arg = PG_GETARG_FLOAT8(0);
+
+ PG_RETURN_FLOAT8(arg + 1.0);
+}
+
+PG_FUNCTION_INFO_V1(makepoint);
+
+Datum
+makepoint(PG_FUNCTION_ARGS)
+{
+ Point *pointx = PG_GETARG_POINT_P(0);
+ Point *pointy = PG_GETARG_POINT_P(1);
+ Point *new_point = (Point *) palloc(sizeof(Point));
+
+ new_point->x = pointx->x;
+ new_point->y = pointy->y;
+
+ PG_RETURN_POINT_P(new_point);
+}
+
+/* By Reference, Variable Length */
+
+PG_FUNCTION_INFO_V1(copytext);
+
+Datum
+copytext(PG_FUNCTION_ARGS)
+{
+ text *t = PG_GETARG_TEXT_PP(0);
+
+ /*
+ * VARSIZE_ANY_EXHDR is the size of the struct in bytes, minus the
+ * VARHDRSZ or VARHDRSZ_SHORT of its header. Construct the copy with a
+ * full-length header.
+ */
+ text *new_t = (text *) palloc(VARSIZE_ANY_EXHDR(t) + VARHDRSZ);
+
+ SET_VARSIZE(new_t, VARSIZE_ANY_EXHDR(t) + VARHDRSZ);
+
+ /*
+ * VARDATA is a pointer to the data region of the new struct. The source
+ * could be a short datum, so retrieve its data through VARDATA_ANY.
+ */
+ memcpy((void *) VARDATA(new_t), /* destination */
+ (void *) VARDATA_ANY(t), /* source */
+ VARSIZE_ANY_EXHDR(t)); /* how many bytes */
+ PG_RETURN_TEXT_P(new_t);
+}
+
+PG_FUNCTION_INFO_V1(concat_text);
+
+Datum
+concat_text(PG_FUNCTION_ARGS)
+{
+ text *arg1 = PG_GETARG_TEXT_PP(0);
+ text *arg2 = PG_GETARG_TEXT_PP(1);
+ int32 arg1_size = VARSIZE_ANY_EXHDR(arg1);
+ int32 arg2_size = VARSIZE_ANY_EXHDR(arg2);
+ int32 new_text_size = arg1_size + arg2_size + VARHDRSZ;
+ text *new_text = (text *) palloc(new_text_size);
+
+ SET_VARSIZE(new_text, new_text_size);
+ memcpy(VARDATA(new_text), VARDATA_ANY(arg1), arg1_size);
+ memcpy(VARDATA(new_text) + arg1_size, VARDATA_ANY(arg2), arg2_size);
+ PG_RETURN_TEXT_P(new_text);
+}
+
+/* Composite types */
+
+PG_FUNCTION_INFO_V1(c_overpaid);
+
+Datum
+c_overpaid(PG_FUNCTION_ARGS)
+{
+ HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
+ int32 limit = PG_GETARG_INT32(1);
+ bool isnull;
+ int32 salary;
+
+ salary = DatumGetInt32(GetAttributeByName(t, "salary", &isnull));
+ if (isnull)
+ PG_RETURN_BOOL(false);
+
+ /*
+ * Alternatively, we might prefer to do PG_RETURN_NULL() for null salary
+ */
+
+ PG_RETURN_BOOL(salary > limit);
+}
diff --git a/src/tutorial/funcs.source b/src/tutorial/funcs.source
new file mode 100644
index 0000000..542b5c8
--- /dev/null
+++ b/src/tutorial/funcs.source
@@ -0,0 +1,160 @@
+---------------------------------------------------------------------------
+--
+-- funcs.sql-
+-- Tutorial on using functions in POSTGRES.
+--
+--
+-- Copyright (c) 1994-5, Regents of the University of California
+--
+-- src/tutorial/funcs.source
+--
+---------------------------------------------------------------------------
+
+-----------------------------
+-- Creating SQL Functions on Base Types
+-- a CREATE FUNCTION statement lets you create a new function that
+-- can be used in expressions (in SELECT, INSERT, etc.). We will start
+-- with functions that return values of base types.
+-----------------------------
+
+--
+-- let's create a simple SQL function that takes no arguments and
+-- returns 1
+
+CREATE FUNCTION one() RETURNS integer
+ AS 'SELECT 1 as ONE' LANGUAGE SQL;
+
+--
+-- functions can be used in any expressions (eg. in the target list or
+-- qualifications)
+
+SELECT one() AS answer;
+
+--
+-- here's how you create a function that takes arguments. The following
+-- function returns the sum of its two arguments:
+
+CREATE FUNCTION add_em(integer, integer) RETURNS integer
+ AS 'SELECT $1 + $2' LANGUAGE SQL;
+
+SELECT add_em(1, 2) AS answer;
+
+-----------------------------
+-- Creating SQL Functions on Composite Types
+-- it is also possible to create functions that return values of
+-- composite types.
+-----------------------------
+
+-- before we create more sophisticated functions, let's populate an EMP
+-- table
+
+CREATE TABLE EMP (
+ name text,
+ salary integer,
+ age integer,
+ cubicle point
+);
+
+INSERT INTO EMP VALUES ('Sam', 1200, 16, '(1,1)');
+INSERT INTO EMP VALUES ('Claire', 5000, 32, '(1,2)');
+INSERT INTO EMP VALUES ('Andy', -1000, 2, '(1,3)');
+INSERT INTO EMP VALUES ('Bill', 4200, 36, '(2,1)');
+INSERT INTO EMP VALUES ('Ginger', 4800, 30, '(2,4)');
+
+-- the argument of a function can also be a tuple. For instance,
+-- double_salary takes a tuple of the EMP table
+
+CREATE FUNCTION double_salary(EMP) RETURNS integer
+ AS 'SELECT $1.salary * 2 AS salary' LANGUAGE SQL;
+
+SELECT name, double_salary(EMP) AS dream
+FROM EMP
+WHERE EMP.cubicle ~= '(2,1)'::point;
+
+-- the return value of a function can also be a tuple. However, make sure
+-- that the expressions in the target list is in the same order as the
+-- columns of EMP.
+
+CREATE FUNCTION new_emp() RETURNS EMP
+ AS 'SELECT ''None''::text AS name,
+ 1000 AS salary,
+ 25 AS age,
+ ''(2,2)''::point AS cubicle'
+ LANGUAGE SQL;
+
+-- you can then project a column out of resulting the tuple by using the
+-- "function notation" for projection columns. (ie. bar(foo) is equivalent
+-- to foo.bar) Note that we don't support new_emp().name at this moment.
+
+SELECT name(new_emp()) AS nobody;
+
+-- let's try one more function that returns tuples
+CREATE FUNCTION high_pay() RETURNS setof EMP
+ AS 'SELECT * FROM EMP where salary > 1500'
+ LANGUAGE SQL;
+
+SELECT name(high_pay()) AS overpaid;
+
+
+-----------------------------
+-- Creating SQL Functions with multiple SQL statements
+-- you can also create functions that do more than just a SELECT.
+-----------------------------
+
+-- you may have noticed that Andy has a negative salary. We'll create a
+-- function that removes employees with negative salaries.
+
+SELECT * FROM EMP;
+
+CREATE FUNCTION clean_EMP () RETURNS integer
+ AS 'DELETE FROM EMP WHERE EMP.salary <= 0;
+ SELECT 1 AS ignore_this'
+ LANGUAGE SQL;
+
+SELECT clean_EMP();
+
+SELECT * FROM EMP;
+
+
+-----------------------------
+-- Creating C Functions
+-- in addition to SQL functions, you can also create C functions.
+-- See funcs.c for the definition of the C functions.
+-----------------------------
+
+CREATE FUNCTION add_one(integer) RETURNS integer
+ AS '_OBJWD_/funcs' LANGUAGE C;
+
+CREATE FUNCTION makepoint(point, point) RETURNS point
+ AS '_OBJWD_/funcs' LANGUAGE C;
+
+CREATE FUNCTION copytext(text) RETURNS text
+ AS '_OBJWD_/funcs' LANGUAGE C;
+
+CREATE FUNCTION c_overpaid(EMP, integer) RETURNS boolean
+ AS '_OBJWD_/funcs' LANGUAGE C;
+
+SELECT add_one(3) AS four;
+
+SELECT makepoint('(1,2)'::point, '(3,4)'::point ) AS newpoint;
+
+SELECT copytext('hello world!');
+
+SELECT name, c_overpaid(EMP, 1500) AS overpaid
+FROM EMP
+WHERE name = 'Bill' or name = 'Sam';
+
+-- remove functions that were created in this file
+
+DROP FUNCTION c_overpaid(EMP, integer);
+DROP FUNCTION copytext(text);
+DROP FUNCTION makepoint(point, point);
+DROP FUNCTION add_one(integer);
+--DROP FUNCTION clean_EMP();
+DROP FUNCTION high_pay();
+DROP FUNCTION new_emp();
+DROP FUNCTION add_em(integer, integer);
+DROP FUNCTION one();
+DROP FUNCTION double_salary(EMP);
+
+DROP TABLE EMP;
diff --git a/src/tutorial/syscat.source b/src/tutorial/syscat.source
new file mode 100644
index 0000000..6458c32
--- /dev/null
+++ b/src/tutorial/syscat.source
@@ -0,0 +1,175 @@
+---------------------------------------------------------------------------
+--
+-- syscat.sql-
+-- sample queries to the system catalogs
+--
+--
+-- Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group
+-- Portions Copyright (c) 1994, Regents of the University of California
+--
+-- src/tutorial/syscat.source
+--
+---------------------------------------------------------------------------
+
+--
+-- Sets the schema search path to pg_catalog first, so that we do not
+-- need to qualify every system object
+--
+SET search_path TO pg_catalog;
+
+-- The LIKE pattern language requires underscores to be escaped, so make
+-- sure the backslashes are not misinterpreted.
+SET standard_conforming_strings TO on;
+
+--
+-- lists the names of all database owners and the name of their database(s)
+--
+SELECT rolname, datname
+ FROM pg_roles, pg_database
+ WHERE pg_roles.oid = datdba
+ ORDER BY rolname, datname;
+
+--
+-- lists all user-defined classes
+--
+SELECT n.nspname, c.relname
+ FROM pg_class c, pg_namespace n
+ WHERE c.relnamespace=n.oid
+ and c.relkind = 'r' -- not indices, views, etc
+ and n.nspname not like 'pg\_%' -- not catalogs
+ and n.nspname != 'information_schema' -- not information_schema
+ ORDER BY nspname, relname;
+
+
+--
+-- lists all simple indices (ie. those that are defined over one simple
+-- column reference)
+--
+SELECT n.nspname AS schema_name,
+ bc.relname AS class_name,
+ ic.relname AS index_name,
+ a.attname
+ FROM pg_namespace n,
+ pg_class bc, -- base class
+ pg_class ic, -- index class
+ pg_index i,
+ pg_attribute a -- att in base
+ WHERE bc.relnamespace = n.oid
+ and i.indrelid = bc.oid
+ and i.indexrelid = ic.oid
+ and i.indkey[0] = a.attnum
+ and i.indnatts = 1
+ and a.attrelid = bc.oid
+ ORDER BY schema_name, class_name, index_name, attname;
+
+
+--
+-- lists the user-defined attributes and their types for all user-defined
+-- classes
+--
+SELECT n.nspname, c.relname, a.attname, format_type(t.oid, null) as typname
+ FROM pg_namespace n, pg_class c,
+ pg_attribute a, pg_type t
+ WHERE n.oid = c.relnamespace
+ and c.relkind = 'r' -- no indices
+ and n.nspname not like 'pg\_%' -- no catalogs
+ and n.nspname != 'information_schema' -- no information_schema
+ and a.attnum > 0 -- no system att's
+ and not a.attisdropped -- no dropped columns
+ and a.attrelid = c.oid
+ and a.atttypid = t.oid
+ ORDER BY nspname, relname, attname;
+
+
+--
+-- lists all user-defined base types (not including array types)
+--
+SELECT n.nspname, r.rolname, format_type(t.oid, null) as typname
+ FROM pg_type t, pg_roles r, pg_namespace n
+ WHERE r.oid = t.typowner
+ and t.typnamespace = n.oid
+ and t.typrelid = 0 -- no complex types
+ and t.typelem = 0 -- no arrays
+ and n.nspname not like 'pg\_%' -- no built-in types
+ and n.nspname != 'information_schema' -- no information_schema
+ ORDER BY nspname, rolname, typname;
+
+
+--
+-- lists all prefix operators
+--
+SELECT n.nspname, o.oprname AS prefix_op,
+ format_type(right_type.oid, null) AS operand,
+ format_type(result.oid, null) AS return_type
+ FROM pg_namespace n, pg_operator o,
+ pg_type right_type, pg_type result
+ WHERE o.oprnamespace = n.oid
+ and o.oprkind = 'l' -- prefix ("left unary")
+ and o.oprright = right_type.oid
+ and o.oprresult = result.oid
+ ORDER BY nspname, operand;
+
+
+--
+-- lists all infix operators
+--
+SELECT n.nspname, o.oprname AS binary_op,
+ format_type(left_type.oid, null) AS left_opr,
+ format_type(right_type.oid, null) AS right_opr,
+ format_type(result.oid, null) AS return_type
+ FROM pg_namespace n, pg_operator o, pg_type left_type,
+ pg_type right_type, pg_type result
+ WHERE o.oprnamespace = n.oid
+ and o.oprkind = 'b' -- infix ("binary")
+ and o.oprleft = left_type.oid
+ and o.oprright = right_type.oid
+ and o.oprresult = result.oid
+ ORDER BY nspname, left_opr, right_opr;
+
+
+--
+-- lists the name, number of arguments and the return type of all user-defined
+-- C functions
+--
+SELECT n.nspname, p.proname, p.pronargs, format_type(t.oid, null) as return_type
+ FROM pg_namespace n, pg_proc p,
+ pg_language l, pg_type t
+ WHERE p.pronamespace = n.oid
+ and n.nspname not like 'pg\_%' -- no catalogs
+ and n.nspname != 'information_schema' -- no information_schema
+ and p.prolang = l.oid
+ and p.prorettype = t.oid
+ and l.lanname = 'c'
+ ORDER BY nspname, proname, pronargs, return_type;
+
+--
+-- lists all aggregate functions and the types to which they can be applied
+--
+SELECT n.nspname, p.proname, format_type(t.oid, null) as typname
+ FROM pg_namespace n, pg_aggregate a,
+ pg_proc p, pg_type t
+ WHERE p.pronamespace = n.oid
+ and a.aggfnoid = p.oid
+ and p.proargtypes[0] = t.oid
+ ORDER BY nspname, proname, typname;
+
+
+--
+-- lists all the operator families that can be used with each access method
+-- as well as the operators that can be used with the respective operator
+-- families
+--
+SELECT am.amname, n.nspname, opf.opfname, opr.oprname
+ FROM pg_namespace n, pg_am am, pg_opfamily opf,
+ pg_amop amop, pg_operator opr
+ WHERE opf.opfnamespace = n.oid
+ and opf.opfmethod = am.oid
+ and amop.amopfamily = opf.oid
+ and amop.amopopr = opr.oid
+ ORDER BY nspname, amname, opfname, oprname;
+
+--
+-- Reset the search path and standard_conforming_strings to their defaults
+--
+RESET search_path;
+RESET standard_conforming_strings;