diff options
Diffstat (limited to 'src/tutorial')
| -rw-r--r-- | src/tutorial/C-code/beard.c | 64 | ||||
| -rw-r--r-- | src/tutorial/C-code/complex.c | 150 | ||||
| -rw-r--r-- | src/tutorial/C-code/funcs.c | 56 | ||||
| -rw-r--r-- | src/tutorial/Makefile | 39 | ||||
| -rw-r--r-- | src/tutorial/README | 24 | ||||
| -rw-r--r-- | src/tutorial/advanced.source | 125 | ||||
| -rw-r--r-- | src/tutorial/basics.source | 188 | ||||
| -rw-r--r-- | src/tutorial/complex.source | 251 | ||||
| -rw-r--r-- | src/tutorial/funcs.source | 158 | ||||
| -rw-r--r-- | src/tutorial/syscat.source | 151 |
10 files changed, 1206 insertions, 0 deletions
diff --git a/src/tutorial/C-code/beard.c b/src/tutorial/C-code/beard.c new file mode 100644 index 00000000000..0fe289c8d2b --- /dev/null +++ b/src/tutorial/C-code/beard.c @@ -0,0 +1,64 @@ +/*------------------------------------------------------------------------- + * + * beard.c-- + * sample routines to use large objects + * + * Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * $Header: /cvsroot/pgsql/src/tutorial/C-code/Attic/beard.c,v 1.1.1.1 1996/07/09 06:22:34 scrappy Exp $ + * + *------------------------------------------------------------------------- + */ + +typedef struct ImageHdr { + int size; +} ImageHdr; + +#define BUFSIZE 10 + +/* + * beard - + * clips lower 1/3 of picture and return as large object + */ +Oid +beard(Oid picture) +{ + Oid beard; + int pic_fd, beard_fd; + ImageHdr ihdr; + char buf[BUFSIZE]; + int cc; + + if ((pic_fd = lo_open(picture, INV_READ)) == -1) + elog(WARN, "Cannot access picture large object"); + + if (lo_read(pic_fd, (char*)&ihdr, sizeof(ihdr)) != sizeof(ihdr)) + elog(WARN, "Picture large object corrupted"); + + beardOffset = (ihdr.size / 3) * 2; + + /* + * new large object + */ + if ((beard = lo_creat(INV_MD)) == 0) /* ?? is this right? */ + elog(WARN, "Cannot create new large object"); + + if ((beard_fd = lo_open(beard, INV_WRITE)) == -1) + elog(WARN, "Cannot access beard large object"); + + lo_lseek(pic_fd, beardOffset, SET_CUR); + while ((cc = lo_read(pic_fd, buf, BUFSIZE)) > 0) { + if (lo_write(beard_fd, buf, cc) != cc) + elog(WARN, "error while writing large object"); + } + + lo_close(pic_fd); + lo_close(beard_fd); + + return beard; +} + + + diff --git a/src/tutorial/C-code/complex.c b/src/tutorial/C-code/complex.c new file mode 100644 index 00000000000..bebdd511d4d --- /dev/null +++ b/src/tutorial/C-code/complex.c @@ -0,0 +1,150 @@ +#include <stdio.h> +/* do not include libpq-fe.h for backend-loaded functions*/ +/* #include "libpq-fe.h" */ +#include "postgres.h" +#include "utils/elog.h" +#include "utils/palloc.h" +#include "utils/mcxt.h" + +typedef struct Complex { + double x; + double y; +} Complex; + +/***************************************************************************** + * Input/Output functions + *****************************************************************************/ + +Complex * +complex_in(char *str) +{ + double x, y; + Complex *result; + + if (sscanf(str, " ( %lf , %lf )", &x, &y) != 2) { + elog(WARN, "complex_in: error in parsing \"%s\"", str); + return NULL; + } + result = (Complex *)palloc(sizeof(Complex)); + result->x = x; + result->y = y; + return (result); +} + +/* + * You might have noticed a slight inconsistency between the following + * declaration and the SQL definition: + * CREATE FUNCTION complex_out(opaque) RETURNS opaque ... + * The reason is that the argument pass into complex_out is really just a + * pointer. POSTGRES thinks all output functions are: + * char *out_func(char *); + */ +char * +complex_out(Complex *complex) +{ + char *result; + + if (complex == NULL) + return(NULL); + + result = (char *) palloc(60); + sprintf(result, "(%lg,%lg)", complex->x, complex->y); + return(result); +} + +/***************************************************************************** + * New Operators + *****************************************************************************/ + +Complex * +complex_add(Complex *a, Complex *b) +{ + Complex *result; + + result = (Complex *)palloc(sizeof(Complex)); + result->x = a->x + b->x; + result->y = a->y + b->y; + return (result); +} + + +/***************************************************************************** + * Operator class for defining B-tree index + *****************************************************************************/ + +#define Mag(c) ((c)->x*(c)->x + (c)->y*(c)->y) + +bool +complex_abs_lt(Complex *a, Complex *b) +{ + double amag = Mag(a), bmag = Mag(b); + return (amag<bmag); +} + +bool +complex_abs_le(Complex *a, Complex *b) +{ + double amag = Mag(a), bmag = Mag(b); + return (amag<=bmag); +} + +bool +complex_abs_eq(Complex *a, Complex *b) +{ + double amag = Mag(a), bmag = Mag(b); + return (amag==bmag); +} + +bool +complex_abs_ge(Complex *a, Complex *b) +{ + double amag = Mag(a), bmag = Mag(b); + return (amag>=bmag); +} + +bool +complex_abs_gt(Complex *a, Complex *b) +{ + double amag = Mag(a), bmag = Mag(b); + return (amag>bmag); +} + +int4 +complex_abs_cmp(Complex *a, Complex *b) +{ + double amag = Mag(a), bmag = Mag(b); + if (a < b) + return -1; + else if (a > b) + return 1; + else + return 0; +} + +/***************************************************************************** + * test code + *****************************************************************************/ + +/* + * You should always test your code separately. Trust me, using POSTGRES to + * debug your C function will be very painful and unproductive. In case of + * POSTGRES crashing, it is impossible to tell whether the bug is in your + * code or POSTGRES's. + */ +void +test_main() +{ + Complex *a; + Complex *b; + + a = complex_in("(4.01, 3.77 )"); + printf("a = %s\n", complex_out(a)); + b = complex_in("(1.0,2.0)"); + printf("b = %s\n", complex_out(b)); + printf("a + b = %s\n", complex_out(complex_add(a,b))); + printf("a < b = %d\n", complex_abs_lt(a,b)); + printf("a <= b = %d\n", complex_abs_le(a,b)); + printf("a = b = %d\n", complex_abs_eq(a,b)); + printf("a >= b = %d\n", complex_abs_ge(a,b)); + printf("a > b = %d\n", complex_abs_gt(a,b)); +} diff --git a/src/tutorial/C-code/funcs.c b/src/tutorial/C-code/funcs.c new file mode 100644 index 00000000000..f91b4d62058 --- /dev/null +++ b/src/tutorial/C-code/funcs.c @@ -0,0 +1,56 @@ +#include <string.h> +#include <stdio.h> +#include "postgres.h" /* for char16, etc. */ +#include "utils/palloc.h" /* for palloc */ +#include "libpq-fe.h" /* for TUPLE */ + +int +add_one(int arg) +{ + return(arg + 1); +} + +char16 * +concat16(char16 *arg1, char16 *arg2) +{ + char16 *new_c16 = (char16 *) palloc(sizeof(char16)); + + memset(new_c16, 0, sizeof(char16)); + (void) strncpy((char*)new_c16, (char*)arg1, 16); + return (char16 *)(strncat((char*)new_c16, (char*)arg2, 16)); +} + +text * +copytext(text *t) +{ + /* + * VARSIZE is the total size of the struct in bytes. + */ + text *new_t = (text *) palloc(VARSIZE(t)); + + memset(new_t, 0, VARSIZE(t)); + + VARSIZE(new_t) = VARSIZE(t); + /* + * VARDATA is a pointer to the data region of the struct. + */ + memcpy((void *) VARDATA(new_t), /* destination */ + (void *) VARDATA(t), /* source */ + VARSIZE(t)-VARHDRSZ); /* how many bytes */ + + return(new_t); +} + +bool +c_overpaid(TUPLE t, /* the current instance of EMP */ + int4 limit) +{ + bool isnull = false; + int4 salary; + + salary = (int4) GetAttributeByName(t, "salary", &isnull); + + if (isnull) + return (false); + return(salary > limit); +} diff --git a/src/tutorial/Makefile b/src/tutorial/Makefile new file mode 100644 index 00000000000..5a29b1468d8 --- /dev/null +++ b/src/tutorial/Makefile @@ -0,0 +1,39 @@ +#------------------------------------------------------------------------- +# +# Makefile-- +# Makefile for tutorial/C-code +# +# Copyright (c) 1994, Regents of the University of California +# +# +# IDENTIFICATION +# $Header: /cvsroot/pgsql/src/tutorial/Makefile,v 1.1.1.1 1996/07/09 06:22:33 scrappy Exp $ +# +#------------------------------------------------------------------------- + +MKDIR= ../mk +include $(MKDIR)/postgres.mk + +VPATH:= $(VPATH):C-code + +# +# build dynamically-loaded object files +# +DLOBJS= complex$(SLSUFF) funcs$(SLSUFF) + +# +# ... plus test query inputs +# +CREATEFILES= $(DLOBJS:%=$(objdir)/%) \ + advanced.sql basics.sql complex.sql funcs.sql syscat.sql + +include $(MKDIR)/postgres.user.mk + +CFLAGS+= -I$(srcdir)/backend + +CLEANFILES+= $(notdir $(CREATEFILES)) + +all:: $(CREATEFILES) + + + diff --git a/src/tutorial/README b/src/tutorial/README new file mode 100644 index 00000000000..b35f7b2b076 --- /dev/null +++ b/src/tutorial/README @@ -0,0 +1,24 @@ +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 and may be named something +different on your system) + +Then, change to the object directory + % cd obj + +and run psql with the -s flag: + % psql -s + +Welcome to the POSTGRES95 interactive sql monitor: + + type \? for help on slash commands + type \q to quit + type \g or terminate with semicolon to execute query + You are currently connected to the database: jolly + +jolly==> + +From within psql, you can try each individual script file by using +the \i <filename> psql command. + diff --git a/src/tutorial/advanced.source b/src/tutorial/advanced.source new file mode 100644 index 00000000000..6e4c7f1e9bd --- /dev/null +++ b/src/tutorial/advanced.source @@ -0,0 +1,125 @@ +--------------------------------------------------------------------------- +-- +-- advanced.sql- +-- more POSTGRES SQL features. (These are not part of the SQL-92 +-- standard.) +-- +-- +-- Copyright (c) 1994, Regents of the University of California +-- +-- $Id: advanced.source,v 1.1.1.1 1996/07/09 06:22:34 scrappy Exp $ +-- +--------------------------------------------------------------------------- + +----------------------------- +-- 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, + altitude int -- (in ft) +) + +CREATE TABLE capitals ( + state char2 +) 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; + +-- like before, a regular query references rows of the base table only + +SELECT name, altitude +FROM cities +WHERE altitude > 500; + +-- on the other hand, you can find all cities, including capitals, that +-- are located at an altitude of 500 'ft or higher by: + +SELECT c.name, c.altitude +FROM cities* c +WHERE c.altitude > 500; + + +----------------------------- +-- Time Travel: +-- this feature allows you to run historical queries. +----------------------------- + +-- first, let's make some changes to the cities table (suppose Mariposa's +-- population grows 10% this year) + +UPDATE cities +SET population = population * 1.1 +WHERE name = 'Mariposa'; + +-- the default time is the current time ('now'): + +SELECT * FROM cities WHERE name = 'Mariposa'; + +-- we can also retrieve the population of Mariposa ever has. ('epoch' is the +-- earliest time representable by the system) + +SELECT name, population +FROM cities['epoch', 'now'] -- can be abbreviated to cities[,] +WHERE name = 'Mariposa'; + + +---------------------- +-- Arrays: +-- attributes can be arrays of base types or user-defined types +---------------------- + +CREATE TABLE sal_emp ( + name text, + pay_by_quarter int4[], + schedule char16[][] +); + +-- insert instances with array attributes. Note the use of braces + +INSERT INTO sal_emp VALUES ( + 'Bill', + '{10000,10000,10000,10000}', + '{{"meeting", "lunch"}, {}}') + +INSERT INTO sal_emp VALUES ( + 'Carol', + '{20000,25000,25000,25000}', + '{{"talk", "consult"}, {"meeting"}}'); + +---------------------- +-- queries on array attributes +---------------------- +SELECT name FROM sal_emp WHERE + sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]; + +-- retrieve third quarter pay of all employees + +SELECT sal_emp.pay_by_quarter[3] FROM sal_emp; + +-- select subarrays + +SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE + sal_emp.name = 'Bill'; + + +-- clean up (you must remove the children first) +DROP TABLE sal_emp +DROP TABLE capitals +DROP TABLE cities; diff --git a/src/tutorial/basics.source b/src/tutorial/basics.source new file mode 100644 index 00000000000..fcda8b3b8c3 --- /dev/null +++ b/src/tutorial/basics.source @@ -0,0 +1,188 @@ +--------------------------------------------------------------------------- +-- +-- basics.sql- +-- Tutorial on the basics (table creation and data manipulation) +-- +-- +-- Copyright (c) 1994, Andrew Yu, University of California +-- +-- $Id: basics.source,v 1.1.1.1 1996/07/09 06:22:34 scrappy Exp $ +-- +--------------------------------------------------------------------------- + +----------------------------- +-- Creating a table: +-- a CREATE TABLE is used to create base tables. POSTGRES SQL has +-- its own set of built-in types. (Note that keywords are case- +-- insensitive but identifiers are case-sensitive.) +----------------------------- + +CREATE TABLE weather ( + city varchar(80), + temp_lo int, -- low temperature + temp_hi int, -- high temperature + prcp float8, -- precipitation + date date +) + +CREATE TABLE cities ( + name varchar(80), + location point +); + +----------------------------- +-- Inserting data: +-- 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, '11/27/1994') + +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. +-- eg. unknown precipitation below) + +INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) + VALUES ('San Francisco', 43, 57, 0.0, '11/29/1994') + +INSERT INTO weather (date, city, temp_hi, temp_lo) + VALUES ('11/29/1994', 'Hayward', 54, 37); + + +----------------------------- +-- Retrieving data: +-- 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 (ie. 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; + +-- 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; + +----------------------------- +-- Retrieving data into other classes: +-- a SELECT ... INTO statement can be used to retrieve data into +-- another class. +----------------------------- + +SELECT * INTO TABLE temp +FROM weather +WHERE city = 'San Francisco' + and prcp > 0.0; + +SELECT * from temp; + +----------------------------- +-- Aggregates +----------------------------- + +SELECT max(temp_lo) +FROM weather; + +-- Aggregate with GROUP BY +SELECT city, max(temp_lo) +FROM weather +GROUP BY city; + +----------------------------- +-- Joining 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. +----------------------------- + +-- 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, W1.temp_hi, + W2.city, W2.temp_lo, W2.temp_hi +FROM weather W1, weather W2 +WHERE W1.temp_lo < W2.temp_lo + and W1.temp_hi > W2.temp_hi; + +-- let's join two tables. The following joins the weather table +-- and the cities table. + +SELECT city, location, prcp, date +FROM weather, cities +WHERE name = city; + +-- 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 w.city, c.location, w.prcp, w.date +FROM weather w, cities c +WHERE c.name = w.city; + +----------------------------- +-- Updating data: +-- 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 > '11/28/1994'; + +SELECT * from weather; + + +----------------------------- +-- Deleting data: +-- a DELETE statement is used for deleting rows from a table. +----------------------------- + +-- suppose you are no longer interested in the weather of Hayward, 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, temp; diff --git a/src/tutorial/complex.source b/src/tutorial/complex.source new file mode 100644 index 00000000000..af8bd26cc2c --- /dev/null +++ b/src/tutorial/complex.source @@ -0,0 +1,251 @@ +--------------------------------------------------------------------------- +-- +-- complex.sql- +-- This file shows how to create a new user-defined type and how to +-- use them. +-- +-- +-- Copyright (c) 1994, Regents of the University of California +-- +-- $Id: complex.source,v 1.1.1.1 1996/07/09 06:22:34 scrappy Exp $ +-- +--------------------------------------------------------------------------- + +----------------------------- +-- Creating a new type: +-- a user-defined type must have an input and an output function. They +-- are user-defined C functions. We are going to create a new type +-- called 'complex' which represents complex numbers. +----------------------------- + +-- Assume the user defined functions are in _OBJWD_/complex.so +-- Look at $PWD/C-code/complex.c for the source. + +-- 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(opaque) + RETURNS complex + AS '_OBJWD_/complex.so' + LANGUAGE 'c'; + +-- the output function 'complex_out' takes the internal representation and +-- converts it into the textual representation. + +CREATE FUNCTION complex_out(opaque) + RETURNS opaque + AS '_OBJWD_/complex.so' + LANGUAGE 'c'; + +-- 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 +); + + +----------------------------- +-- Using the new type: +-- user-defined types can be use like ordinary built-in types. +----------------------------- + +-- eg. we can use it in a schema + +CREATE TABLE test_complex ( + a complex, + b complex +); + +-- data for user-defined type 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. +-- (Operators can be reused with different number and types of +-- arguments.) +----------------------------- + +-- first, define a function complex_add (also in C-code/complex.c) +CREATE FUNCTION complex_add(complex, complex) + RETURNS complex + AS '_OBJWD_/complex.so' + LANGUAGE 'c'; + +-- we can now define the operator. We show a binary operator here but you +-- can also define unary operators by omitting either of leftarg or rightarg. +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 some what +-- cryptic but the idea is to express the aggregate in terms of state +-- transition functions. +----------------------------- + +CREATE AGGREGATE complex_sum ( + sfunc1 = complex_add, + basetype = complex, + stype1 = complex, + initcond1 = '(0,0)' +); + +SELECT complex_sum(a) FROM test_complex; + + +------------------------------------------------------------------------------- +-- ATTENTION! ATTENTION! ATTENTION! -- +-- YOU MAY SKIP THE SECTION BELOW ON INTERFACING WITH INDICIES. YOU DON'T -- +-- NEED THE FOLLOWING IF YOU DON'T USE INDICIES WITH NEW DATA TYPES. -- +------------------------------------------------------------------------------- + +SELECT 'READ ABOVE!' AS STOP; + +----------------------------- +-- Interfacing New Types with Indices: +-- We cannot define a secondary index (eg. a B-tree) over the new type +-- yet. We need to modify a few system catalogs to show POSTGRES how +-- to use the new type. Unfortunately, there is no simple command to +-- do this. Please bear with me. +----------------------------- + +-- first, define the required operators +CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool + AS '_OBJWD_/complex.so' LANGUAGE 'c' +CREATE FUNCTION complex_abs_le(complex, complex) RETURNS bool + AS '_OBJWD_/complex.so' LANGUAGE 'c' +CREATE FUNCTION complex_abs_eq(complex, complex) RETURNS bool + AS '_OBJWD_/complex.so' LANGUAGE 'c' +CREATE FUNCTION complex_abs_ge(complex, complex) RETURNS bool + AS '_OBJWD_/complex.so' LANGUAGE 'c' +CREATE FUNCTION complex_abs_gt(complex, complex) RETURNS bool + AS '_OBJWD_/complex.so' LANGUAGE 'c'; + +-- the restrict and join selectivity functions are bogus (notice we only +-- have intltsel, eqsel and intgtsel) +CREATE OPERATOR < ( + leftarg = complex, rightarg = complex, procedure = complex_abs_lt, + restrict = intltsel, join = intltjoinsel +) +CREATE OPERATOR <= ( + leftarg = complex, rightarg = complex, procedure = complex_abs_le, + restrict = intltsel, join = intltjoinsel +) +CREATE OPERATOR = ( + leftarg = complex, rightarg = complex, procedure = complex_abs_eq, + restrict = eqsel, join = eqjoinsel +) +CREATE OPERATOR >= ( + leftarg = complex, rightarg = complex, procedure = complex_abs_ge, + restrict = intgtsel, join = intgtjoinsel +) +CREATE OPERATOR > ( + leftarg = complex, rightarg = complex, procedure = complex_abs_gt, + restrict = intgtsel, join = intgtjoinsel +); + +INSERT INTO pg_opclass VALUES ('complex_abs_ops') + +SELECT oid, opcname FROM pg_opclass WHERE opcname = 'complex_abs_ops'; + +SELECT o.oid AS opoid, o.oprname +INTO TABLE complex_ops_tmp +FROM pg_operator o, pg_type t +WHERE o.oprleft = t.oid and o.oprright = t.oid + and t.typname = 'complex'; + +-- make sure we have the right operators +SELECT * from complex_ops_tmp; + +INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy, + amopselect, amopnpages) + SELECT am.oid, opcl.oid, c.opoid, 1, + 'btreesel'::regproc, 'btreenpage'::regproc + FROM pg_am am, pg_opclass opcl, complex_ops_tmp c + WHERE amname = 'btree' and opcname = 'complex_abs_ops' + and c.oprname = '<'; + +INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy, + amopselect, amopnpages) + SELECT am.oid, opcl.oid, c.opoid, 2, + 'btreesel'::regproc, 'btreenpage'::regproc + FROM pg_am am, pg_opclass opcl, complex_ops_tmp c + WHERE amname = 'btree' and opcname = 'complex_abs_ops' + and c.oprname = '<='; + +INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy, + amopselect, amopnpages) + SELECT am.oid, opcl.oid, c.opoid, 3, + 'btreesel'::regproc, 'btreenpage'::regproc + FROM pg_am am, pg_opclass opcl, complex_ops_tmp c + WHERE amname = 'btree' and opcname = 'complex_abs_ops' + and c.oprname = '='; + +INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy, + amopselect, amopnpages) + SELECT am.oid, opcl.oid, c.opoid, 4, + 'btreesel'::regproc, 'btreenpage'::regproc + FROM pg_am am, pg_opclass opcl, complex_ops_tmp c + WHERE amname = 'btree' and opcname = 'complex_abs_ops' + and c.oprname = '>='; + +INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy, + amopselect, amopnpages) + SELECT am.oid, opcl.oid, c.opoid, 5, + 'btreesel'::regproc, 'btreenpage'::regproc + FROM pg_am am, pg_opclass opcl, complex_ops_tmp c + WHERE amname = 'btree' and opcname = 'complex_abs_ops' + and c.oprname = '>'; + +DROP table complex_ops_tmp; + +-- +CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4 + AS '_OBJWD_/complex.so' LANGUAGE 'c'; + +SELECT oid, proname FROM pg_proc WHERE proname = 'complex_abs_cmp'; + +INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) + SELECT am.oid, opcl.oid, pro.oid, 1 + FROM pg_am am, pg_opclass opcl, pg_proc pro + WHERE amname = 'btree' and opcname = 'complex_abs_ops' + and proname = 'complex_abs_cmp'; + +-- now, we can define a btree index on complex types. First, let's populate +-- the table (THIS DOESN'T ACTUALLY WORK. YOU NEED MANY MORE TUPLES.) +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)';
\ No newline at end of file diff --git a/src/tutorial/funcs.source b/src/tutorial/funcs.source new file mode 100644 index 00000000000..00f256ea859 --- /dev/null +++ b/src/tutorial/funcs.source @@ -0,0 +1,158 @@ +--------------------------------------------------------------------------- +-- +-- funcs.sql- +-- Tutorial on using functions in POSTGRES. +-- +-- +-- Copyright (c) 1994-5, Regents of the University of California +-- +-- $Id: funcs.source,v 1.1.1.1 1996/07/09 06:22:34 scrappy Exp $ +-- +--------------------------------------------------------------------------- + +----------------------------- +-- 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 int4 + 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(int4, int4) RETURNS int4 + 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 int4, + age int4, + dept char16 +); + +INSERT INTO EMP VALUES ('Sam', 1200, 16, 'toy') +INSERT INTO EMP VALUES ('Claire', 5000, 32, 'shoe') +INSERT INTO EMP VALUES ('Andy', -1000, 2, 'candy') +INSERT INTO EMP VALUES ('Bill', 4200, 36, 'shoe') +INSERT INTO EMP VALUES ('Ginger', 4800, 30, 'candy'); + +-- 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 int4 + AS 'SELECT $1.salary * 2 AS salary' LANGUAGE 'sql'; + +SELECT name, double_salary(EMP) AS dream +FROM EMP +WHERE EMP.dept = 'toy'; + +-- 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, + \'none\'::char16 AS dept' + 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 int4 + 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 C-code/funcs.c for the definition of the C functions. +----------------------------- + +CREATE FUNCTION add_one(int4) RETURNS int4 + AS '_OBJWD_/funcs.so' LANGUAGE 'c'; + +CREATE FUNCTION concat16(char16, char16) RETURNS char16 + AS '_OBJWD_/funcs.so' LANGUAGE 'c'; + +CREATE FUNCTION copytext(text) RETURNS text + AS '_OBJWD_/funcs.so' LANGUAGE 'c'; + +CREATE FUNCTION c_overpaid(EMP, int4) RETURNS bool + AS '_OBJWD_/funcs.so' LANGUAGE 'c'; + +SELECT add_one(3) AS four; + +SELECT concat16('abc', 'xyz') AS newchar16; + +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, int4) +DROP FUNCTION copytext(text) +DROP FUNCTION concat16(char16,char16) +DROP FUNCTION add_one(int4) +DROP FUNCTION clean_EMP() +DROP FUNCTION new_emp() +DROP FUNCTION add_em(int4, int4) +DROP FUNCTION one(); + +DROP TABLE EMP; diff --git a/src/tutorial/syscat.source b/src/tutorial/syscat.source new file mode 100644 index 00000000000..90ed0e4ec5a --- /dev/null +++ b/src/tutorial/syscat.source @@ -0,0 +1,151 @@ +--------------------------------------------------------------------------- +-- +-- syscat.sql- +-- sample queries to the system catalogs +-- +-- +-- Copyright (c) 1994, Regents of the University of California +-- +-- $Id: syscat.source,v 1.1.1.1 1996/07/09 06:22:34 scrappy Exp $ +-- +--------------------------------------------------------------------------- + +-- +-- lists the name of all database adminstrators and the name of their +-- database(s) +-- +SELECT usename, datname + FROM pg_user, pg_database + WHERE usesysid = int2in(int4out(datdba)) + ORDER BY usename, datname; + +-- +-- lists all user-defined classes +-- +SELECT relname + FROM pg_class + WHERE relkind = 'r' -- not indices + and relname !~ '^pg_' -- not catalogs + and relname !~ '^Inv' -- not large objects + ORDER BY relname; + + +-- +-- lists all simple indicies (ie. those that are not defined over a function +-- of several attributes) +-- +SELECT bc.relname AS class_name, + ic.relname AS index_name, + a.attname + FROM pg_class bc, -- base class + pg_class ic, -- index class + pg_index i, + pg_attribute a -- att in base + WHERE i.indrelid = bc.oid + and i.indexrelid = ic.oid + and i.indkey[0] = a.attnum + and a.attrelid = bc.oid + and i.indproc = '0'::oid -- no functional indices + ORDER BY class_name, index_name, attname; + + +-- +-- lists the user-defined attributes and their types for all user-defined +-- classes +-- +SELECT c.relname, a.attname, t.typname + FROM pg_class c, pg_attribute a, pg_type t + WHERE c.relkind = 'r' -- no indices + and c.relname !~ '^pg_' -- no catalogs + and c.relname !~ '^Inv' -- no large objects + and a.attnum > 0 -- no system att's + and a.attrelid = c.oid + and a.atttypid = t.oid + ORDER BY relname, attname; + + +-- +-- lists all user-defined base types (not includeing array types) +-- +SELECT u.usename, t.typname + FROM pg_type t, pg_user u + WHERE u.usesysid = int2in(int4out(t.typowner)) + and t.typrelid = '0'::oid -- no complex types + and t.typelem = '0'::oid -- no arrays + and u.usename <> 'postgres' + ORDER BY usename, typname; + + +-- +-- lists all left unary operators +-- +SELECT o.oprname AS left_unary, + right.typname AS operand, + result.typname AS return_type + FROM pg_operator o, pg_type right, pg_type result + WHERE o.oprkind = 'l' -- left unary + and o.oprright = right.oid + and o.oprresult = result.oid + ORDER BY operand; + + +-- +-- lists all right unary operators +-- +SELECT o.oprname AS right_unary, + left.typname AS operand, + result.typname AS return_type + FROM pg_operator o, pg_type left, pg_type result + WHERE o.oprkind = 'r' -- right unary + and o.oprleft = left.oid + and o.oprresult = result.oid + ORDER BY operand; + +-- +-- lists all binary operators +-- +SELECT o.oprname AS binary_op, + left.typname AS left_opr, + right.typname AS right_opr, + result.typname AS return_type + FROM pg_operator o, pg_type left, pg_type right, pg_type result + WHERE o.oprkind = 'b' -- binary + and o.oprleft = left.oid + and o.oprright = right.oid + and o.oprresult = result.oid + ORDER BY left_opr, right_opr; + + +-- +-- lists the name, number of arguments and the return type of all user-defined +-- C functions +-- +SELECT p.proname, p.pronargs, t.typname + FROM pg_proc p, pg_language l, pg_type t + WHERE p.prolang = l.oid + and p.prorettype = t.oid + and l.lanname = 'c' + ORDER BY proname; + +-- +-- lists all aggregate functions and the types to which they can be applied +-- +SELECT a.aggname, t.typname + FROM pg_aggregate a, pg_type t + WHERE a.aggbasetype = t.oid + ORDER BY aggname, typname; + + +-- +-- lists all the operator classes that can be used with each access method +-- as well as the operators that cn be used with the respective operator +-- classes +-- +SELECT am.amname, opc.opcname, opr.oprname + FROM pg_am am, pg_amop amop, pg_opclass opc, pg_operator opr + WHERE amop.amopid = am.oid + and amop.amopclaid = opc.oid + and amop.amopopr = opr.oid + ORDER BY amname, opcname, oprname; + + |