diff options
Diffstat (limited to 'src/tutorial')
| -rw-r--r-- | src/tutorial/.gitignore | 5 | ||||
| -rw-r--r-- | src/tutorial/Makefile | 33 | ||||
| -rw-r--r-- | src/tutorial/README | 16 | ||||
| -rw-r--r-- | src/tutorial/advanced.source | 60 | ||||
| -rw-r--r-- | src/tutorial/basics.source | 215 | ||||
| -rw-r--r-- | src/tutorial/complex.c | 209 | ||||
| -rw-r--r-- | src/tutorial/complex.source | 228 | ||||
| -rw-r--r-- | src/tutorial/funcs.c | 126 | ||||
| -rw-r--r-- | src/tutorial/funcs.source | 160 | ||||
| -rw-r--r-- | src/tutorial/syscat.source | 175 |
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; |