diff options
Diffstat (limited to 'src/tutorial')
| -rw-r--r-- | src/tutorial/Makefile | 34 | ||||
| -rw-r--r-- | src/tutorial/README | 11 | ||||
| -rw-r--r-- | src/tutorial/advanced.source | 60 | ||||
| -rw-r--r-- | src/tutorial/basics.source | 204 | ||||
| -rw-r--r-- | src/tutorial/beard.c | 78 | ||||
| -rw-r--r-- | src/tutorial/complex.c | 180 | ||||
| -rw-r--r-- | src/tutorial/complex.source | 293 | ||||
| -rw-r--r-- | src/tutorial/funcs.c | 107 | ||||
| -rw-r--r-- | src/tutorial/funcs.source | 162 | ||||
| -rw-r--r-- | src/tutorial/funcs_new.c | 133 | ||||
| -rw-r--r-- | src/tutorial/syscat.source | 149 |
11 files changed, 0 insertions, 1411 deletions
diff --git a/src/tutorial/Makefile b/src/tutorial/Makefile deleted file mode 100644 index 07ed26f3de6..00000000000 --- a/src/tutorial/Makefile +++ /dev/null @@ -1,34 +0,0 @@ -#------------------------------------------------------------------------- -# -# Makefile-- -# Makefile for tutorial -# -# IDENTIFICATION -# $Header: /cvsroot/pgsql/src/tutorial/Makefile,v 1.14 2001/10/26 20:45:33 tgl Exp $ -# -#------------------------------------------------------------------------- - -subdir = src/tutorial -top_builddir = ../.. -include $(top_builddir)/src/Makefile.global - -override CFLAGS+= $(CFLAGS_SL) -override DLLLIBS := $(BE_DLLLIBS) $(DLLLIBS) - -# -# DLOBJS are the dynamically-loaded object files. The "funcs" queries -# include CREATE FUNCTIONs that load routines from these files. -# -DLOBJS= complex$(DLSUFFIX) funcs$(DLSUFFIX) - -QUERIES= advanced.sql basics.sql complex.sql funcs.sql syscat.sql - -all: $(DLOBJS) $(QUERIES) - -%.sql: %.source - rm -f $@; \ - C=`pwd`; \ - sed -e "s:_OBJWD_:$$C:g" < $< > $@ - -clean: - rm -f $(DLOBJS) $(QUERIES) diff --git a/src/tutorial/README b/src/tutorial/README deleted file mode 100644 index 6d05cb578f4..00000000000 --- a/src/tutorial/README +++ /dev/null @@ -1,11 +0,0 @@ -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 deleted file mode 100644 index 7dd3ab1bb40..00000000000 --- a/src/tutorial/advanced.source +++ /dev/null @@ -1,60 +0,0 @@ ---------------------------------------------------------------------------- --- --- advanced.sql- --- Tutorial on advanced more PostgreSQL features --- --- --- Copyright (c) 1994, Regents of the University of California --- --- $Id: advanced.source,v 1.5 2002/04/11 21:18:50 tgl 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 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 altitude of 500 ft or higher by: - -SELECT c.name, c.altitude -FROM cities c -WHERE c.altitude > 500; - --- To scan rows of the parent table only, use ONLY: - -SELECT name, altitude -FROM ONLY cities -WHERE altitude > 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 deleted file mode 100644 index 748323eb8e7..00000000000 --- a/src/tutorial/basics.source +++ /dev/null @@ -1,204 +0,0 @@ ---------------------------------------------------------------------------- --- --- basics.sql- --- Tutorial on the basics (table creation and data manipulation) --- --- --- Copyright (c) 1994, Andrew Yu, University of California --- --- $Id: basics.source,v 1.4 2001/09/02 23:27:50 petere Exp $ --- ---------------------------------------------------------------------------- - ------------------------------ --- 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; - --- 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, cities - WHERE city = name; - --- This prevents a duplicate city name column: - -SELECT city, temp_lo, temp_hi, prcp, date, location - FROM weather, cities - WHERE 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, cities - WHERE cities.name = weather.city; - --- JOIN syntax - -SELECT * - FROM weather JOIN cities ON (weather.city = cities.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, 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; - - ------------------------------ --- 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, max(temp_lo) - FROM weather - GROUP BY city; - --- ... and HAVING -SELECT city, max(temp_lo) - FROM weather - GROUP BY city - HAVING max(temp_lo) < 40; - - ------------------------------ --- 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/beard.c b/src/tutorial/beard.c deleted file mode 100644 index 845c5db6b3f..00000000000 --- a/src/tutorial/beard.c +++ /dev/null @@ -1,78 +0,0 @@ -/*------------------------------------------------------------------------- - * - * beard.c - * sample routines to use large objects - * - * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group - * Portions Copyright (c) 1994, Regents of the University of California - * - * - * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/tutorial/Attic/beard.c,v 1.8 2002/06/20 20:29:54 momjian 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; - - pic_fd = DatumGetInt32(DirectFunctionCall2(lo_open, - ObjectIdGetDatum(picture), - Int32GetDatum(INV_READ))); - if (pic_fd < 0) - elog(ERROR, "Cannot access picture large object"); - - if (lo_read(pic_fd, (char *) &ihdr, sizeof(ihdr)) != sizeof(ihdr)) - elog(ERROR, "Picture large object corrupted"); - - beardOffset = (ihdr.size / 3) * 2; - - /* - * new large object - */ - beard = DatumGetObjectId(DirectFunctionCall1(lo_creat, - Int32GetDatum(INV_MD))); - if (beard == InvalidOid) - elog(ERROR, "Cannot create new large object"); - - beard_fd = DatumGetInt32(DirectFunctionCall2(lo_open, - ObjectIdGetDatum(beard), - Int32GetDatum(INV_WRITE))); - if (beard_fd < 0) - elog(ERROR, "Cannot access beard large object"); - - if (DatumGetInt32(DirectFunctionCall3(lo_lseek, - Int32GetDatum(pic_fd), - Int32GetDatum(beardOffset), - Int32GetDatum(SEEK_SET))) < 0) - elog(ERROR, "Cannot seek in picture large object"); - - while ((cc = lo_read(pic_fd, buf, BUFSIZE)) > 0) - { - if (lo_write(beard_fd, buf, cc) != cc) - elog(ERROR, "error while writing large object"); - } - - DirectFunctionCall1(lo_close, Int32GetDatum(pic_fd)); - DirectFunctionCall1(lo_close, Int32GetDatum(beard_fd)); - - return beard; -} diff --git a/src/tutorial/complex.c b/src/tutorial/complex.c deleted file mode 100644 index 2dd4e6e6078..00000000000 --- a/src/tutorial/complex.c +++ /dev/null @@ -1,180 +0,0 @@ -/****************************************************************************** - 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" - -typedef struct Complex -{ - double x; - double y; -} Complex; - -/* These prototypes declare the requirements that Postgres places on these - user written functions. -*/ -Complex *complex_in(char *str); -char *complex_out(Complex * complex); -Complex *complex_add(Complex * a, Complex * b); -bool complex_abs_lt(Complex * a, Complex * b); -bool complex_abs_le(Complex * a, Complex * b); -bool complex_abs_eq(Complex * a, Complex * b); -bool complex_abs_ge(Complex * a, Complex * b); -bool complex_abs_gt(Complex * a, Complex * b); -int4 complex_abs_cmp(Complex * a, Complex * b); - - -/***************************************************************************** - * Input/Output functions - *****************************************************************************/ - -Complex * -complex_in(char *str) -{ - double x, - y; - Complex *result; - - if (sscanf(str, " ( %lf , %lf )", &x, &y) != 2) - { - elog(ERROR, "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, "(%g,%g)", 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 (amag < bmag) - return -1; - else if (amag > bmag) - 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(void); -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/complex.source b/src/tutorial/complex.source deleted file mode 100644 index 6fbaaf89dbd..00000000000 --- a/src/tutorial/complex.source +++ /dev/null @@ -1,293 +0,0 @@ ---------------------------------------------------------------------------- --- --- complex.sql- --- This file shows how to create a new user-defined type and how to --- use this new type. --- --- --- Copyright (c) 1994, Regents of the University of California --- --- $Id: complex.source,v 1.12 2002/04/17 20:57:57 tgl 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$DLSUFFIX --- (we do not want to assume this is in the dynamic loader search path) --- Look at $PWD/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' - 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' - 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 complex.c) -CREATE FUNCTION complex_add(complex, complex) - RETURNS complex - AS '_OBJWD_/complex' - 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 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; - - -------------------------------------------------------------------------------- --- ATTENTION! ATTENTION! ATTENTION! -- --- YOU MAY SKIP THE SECTION BELOW ON INTERFACING WITH INDICES. YOU DON'T -- --- NEED THE FOLLOWING IF YOU DON'T USE INDICES 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' LANGUAGE 'c'; -CREATE FUNCTION complex_abs_le(complex, complex) RETURNS bool - AS '_OBJWD_/complex' LANGUAGE 'c'; -CREATE FUNCTION complex_abs_eq(complex, complex) RETURNS bool - AS '_OBJWD_/complex' LANGUAGE 'c'; -CREATE FUNCTION complex_abs_ge(complex, complex) RETURNS bool - AS '_OBJWD_/complex' LANGUAGE 'c'; -CREATE FUNCTION complex_abs_gt(complex, complex) RETURNS bool - AS '_OBJWD_/complex' LANGUAGE 'c'; - -CREATE OPERATOR < ( - leftarg = complex, rightarg = complex, procedure = complex_abs_lt, - restrict = scalarltsel, join = scalarltjoinsel -); -CREATE OPERATOR <= ( - leftarg = complex, rightarg = complex, procedure = complex_abs_le, - restrict = scalarltsel, join = scalarltjoinsel -); -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 = scalargtsel, join = scalargtjoinsel -); -CREATE OPERATOR > ( - leftarg = complex, rightarg = complex, procedure = complex_abs_gt, - restrict = scalargtsel, join = scalargtjoinsel -); - -INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype) - VALUES ( - (SELECT oid FROM pg_am WHERE amname = 'btree'), - 'complex_abs_ops', - (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), - 1, -- UID of superuser is hardwired to 1 as of PG 7.3 - (SELECT oid FROM pg_type WHERE typname = 'complex'), - true, - 0); - -SELECT oid, * - FROM pg_opclass WHERE opcname = 'complex_abs_ops'; - -SELECT o.oid AS opoid, o.oprname -INTO TEMP 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 (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 1, false, c.opoid - FROM pg_opclass opcl, complex_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') - and opcname = 'complex_abs_ops' - and c.oprname = '<'; - -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 2, false, c.opoid - FROM pg_opclass opcl, complex_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') - and opcname = 'complex_abs_ops' - and c.oprname = '<='; - -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 3, false, c.opoid - FROM pg_opclass opcl, complex_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') - and opcname = 'complex_abs_ops' - and c.oprname = '='; - -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 4, false, c.opoid - FROM pg_opclass opcl, complex_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') - and opcname = 'complex_abs_ops' - and c.oprname = '>='; - -INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr) - SELECT opcl.oid, 5, false, c.opoid - FROM pg_opclass opcl, complex_ops_tmp c - WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') - and opcname = 'complex_abs_ops' - and c.oprname = '>'; - --- -CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4 - AS '_OBJWD_/complex' LANGUAGE 'c'; - -SELECT oid, proname FROM pg_proc WHERE proname = 'complex_abs_cmp'; - -INSERT INTO pg_amproc (amopclaid, amprocnum, amproc) - SELECT opcl.oid, 1, pro.oid - FROM pg_opclass opcl, pg_proc pro - WHERE - opcamid = (SELECT oid FROM pg_am 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. 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)'; - -DELETE FROM pg_amop WHERE - amopclaid = (SELECT oid FROM pg_opclass WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') - and opcname = 'complex_abs_ops'); - -DELETE FROM pg_amproc WHERE - amopclaid = (SELECT oid FROM pg_opclass WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') - and opcname = 'complex_abs_ops'); - -DELETE FROM pg_opclass WHERE - opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree') - and opcname = 'complex_abs_ops'; - -DROP FUNCTION complex_in(opaque); -DROP FUNCTION complex_out(opaque); -DROP FUNCTION complex_add(complex, complex); -DROP FUNCTION complex_abs_lt(complex, complex); -DROP FUNCTION complex_abs_le(complex, complex); -DROP FUNCTION complex_abs_eq(complex, complex); -DROP FUNCTION complex_abs_ge(complex, complex); -DROP FUNCTION complex_abs_gt(complex, complex); -DROP FUNCTION complex_abs_cmp(complex, complex); -DROP OPERATOR + (complex, complex); -DROP OPERATOR < (complex, complex); -DROP OPERATOR <= (complex, complex); -DROP OPERATOR = (complex, complex); -DROP OPERATOR >= (complex, complex); -DROP OPERATOR > (complex, complex); -DROP AGGREGATE complex_sum (complex); -DROP TYPE complex; -DROP TABLE test_complex, complex_ops_tmp; diff --git a/src/tutorial/funcs.c b/src/tutorial/funcs.c deleted file mode 100644 index d2c123a0094..00000000000 --- a/src/tutorial/funcs.c +++ /dev/null @@ -1,107 +0,0 @@ -/****************************************************************************** - 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. - - NOTE: this file shows examples of "old style" function call conventions. - See funcs_new.c for examples of "new style". -*****************************************************************************/ - -#include "postgres.h" /* general Postgres declarations */ - -#include "executor/executor.h" /* for GetAttributeByName() */ -#include "utils/geo_decls.h" /* for point type */ - - -/* These prototypes just prevent possible warnings from gcc. */ - -int add_one(int arg); -float8 *add_one_float8(float8 *arg); -Point *makepoint(Point *pointx, Point *pointy); -text *copytext(text *t); -text *concat_text(text *arg1, text *arg2); -bool c_overpaid(TupleTableSlot *t, /* the current instance of EMP */ - int32 limit); - - -/* By Value */ - -int -add_one(int arg) -{ - return arg + 1; -} - -/* By Reference, Fixed Length */ - -float8 * -add_one_float8(float8 *arg) -{ - float8 *result = (float8 *) palloc(sizeof(float8)); - - *result = *arg + 1.0; - - return result; -} - -Point * -makepoint(Point *pointx, Point *pointy) -{ - Point *new_point = (Point *) palloc(sizeof(Point)); - - new_point->x = pointx->x; - new_point->y = pointy->y; - - return new_point; -} - -/* By Reference, Variable Length */ - -text * -copytext(text *t) -{ - /* - * VARSIZE is the total size of the struct in bytes. - */ - text *new_t = (text *) palloc(VARSIZE(t)); - - VARATT_SIZEP(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; -} - -text * -concat_text(text *arg1, text *arg2) -{ - int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ; - text *new_text = (text *) palloc(new_text_size); - - memset((void *) new_text, 0, new_text_size); - VARATT_SIZEP(new_text) = new_text_size; - strncpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1) - VARHDRSZ); - strncat(VARDATA(new_text), VARDATA(arg2), VARSIZE(arg2) - VARHDRSZ); - return new_text; -} - -/* Composite types */ - -bool -c_overpaid(TupleTableSlot *t, /* the current instance of EMP */ - int32 limit) -{ - bool isnull; - int32 salary; - - salary = DatumGetInt32(GetAttributeByName(t, "salary", &isnull)); - if (isnull) - return (false); - return salary > limit; -} diff --git a/src/tutorial/funcs.source b/src/tutorial/funcs.source deleted file mode 100644 index 8438e6daece..00000000000 --- a/src/tutorial/funcs.source +++ /dev/null @@ -1,162 +0,0 @@ ---------------------------------------------------------------------------- --- --- funcs.sql- --- Tutorial on using functions in POSTGRES. --- --- --- Copyright (c) 1994-5, Regents of the University of California --- --- $Id: funcs.source,v 1.5 2001/10/26 20:45:33 tgl 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, - 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 int4 - 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. --- --- 14MAR99 Clark Evans: Does not quite work, commented out for now. --- ------------------------------ - --- 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 funcs.c for the definition of the C functions. ------------------------------ - -CREATE FUNCTION add_one(int4) RETURNS int4 - 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, int4) RETURNS bool - 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, int4); -DROP FUNCTION copytext(text); -DROP FUNCTION makepoint(point,point); -DROP FUNCTION add_one(int4); -DROP FUNCTION clean_EMP(); -DROP FUNCTION high_pay(); -DROP FUNCTION new_emp(); -DROP FUNCTION add_em(int4, int4); -DROP FUNCTION one(); - -DROP TABLE EMP; diff --git a/src/tutorial/funcs_new.c b/src/tutorial/funcs_new.c deleted file mode 100644 index c9413096bcf..00000000000 --- a/src/tutorial/funcs_new.c +++ /dev/null @@ -1,133 +0,0 @@ -/****************************************************************************** - 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. - - NOTE: this file shows examples of "new style" function call conventions. - See funcs.c for examples of "old style". -*****************************************************************************/ - -#include "postgres.h" /* general Postgres declarations */ - -#include "fmgr.h" /* for argument/result macros */ -#include "executor/executor.h" /* for GetAttributeByName() */ -#include "utils/geo_decls.h" /* for point type */ - - -/* These prototypes just prevent possible warnings from gcc. */ - -Datum add_one(PG_FUNCTION_ARGS); -Datum add_one_float8(PG_FUNCTION_ARGS); -Datum makepoint(PG_FUNCTION_ARGS); -Datum copytext(PG_FUNCTION_ARGS); -Datum concat_text(PG_FUNCTION_ARGS); -Datum c_overpaid(PG_FUNCTION_ARGS); - - -/* 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_P(0); - - /* - * VARSIZE is the total size of the struct in bytes. - */ - text *new_t = (text *) palloc(VARSIZE(t)); - - VARATT_SIZEP(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 */ - PG_RETURN_TEXT_P(new_t); -} - -PG_FUNCTION_INFO_V1(concat_text); - -Datum -concat_text(PG_FUNCTION_ARGS) -{ - text *arg1 = PG_GETARG_TEXT_P(0); - text *arg2 = PG_GETARG_TEXT_P(1); - int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ; - text *new_text = (text *) palloc(new_text_size); - - memset((void *) new_text, 0, new_text_size); - VARATT_SIZEP(new_text) = new_text_size; - strncpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1) - VARHDRSZ); - strncat(VARDATA(new_text), VARDATA(arg2), VARSIZE(arg2) - VARHDRSZ); - PG_RETURN_TEXT_P(new_text); -} - -/* Composite types */ - -PG_FUNCTION_INFO_V1(c_overpaid); - -Datum -c_overpaid(PG_FUNCTION_ARGS) -{ - TupleTableSlot *t = (TupleTableSlot *) PG_GETARG_POINTER(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/syscat.source b/src/tutorial/syscat.source deleted file mode 100644 index 4ef6d513bf4..00000000000 --- a/src/tutorial/syscat.source +++ /dev/null @@ -1,149 +0,0 @@ ---------------------------------------------------------------------------- --- --- syscat.sql- --- sample queries to the system catalogs --- --- --- Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group --- Portions Copyright (c) 1994, Regents of the University of California --- --- $Id: syscat.source,v 1.7 2002/06/20 20:29:54 momjian 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 = datdba - ORDER BY usename, datname; - --- --- lists all user-defined classes --- -SELECT relname - FROM pg_class - WHERE relkind = 'r' -- not indices, views, etc - and relname !~ '^pg_' -- not catalogs - ORDER BY relname; - - --- --- lists all simple indices (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 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 including array types) --- -SELECT u.usename, t.typname - FROM pg_type t, pg_user u - WHERE u.usesysid = 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_type.typname AS operand, - result.typname AS return_type - FROM pg_operator o, pg_type right_type, pg_type result - WHERE o.oprkind = 'l' -- left unary - and o.oprright = right_type.oid - and o.oprresult = result.oid - ORDER BY operand; - - --- --- lists all right unary operators --- -SELECT o.oprname AS right_unary, - left_type.typname AS operand, - result.typname AS return_type - FROM pg_operator o, pg_type left_type, pg_type result - WHERE o.oprkind = 'r' -- right unary - and o.oprleft = left_type.oid - and o.oprresult = result.oid - ORDER BY operand; - --- --- lists all binary operators --- -SELECT o.oprname AS binary_op, - left_type.typname AS left_opr, - right_type.typname AS right_opr, - result.typname AS return_type - FROM pg_operator o, pg_type left_type, pg_type right_type, pg_type result - WHERE o.oprkind = 'b' -- binary - and o.oprleft = left_type.oid - and o.oprright = right_type.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 p.proname, t.typname - FROM pg_aggregate a, pg_proc p, pg_type t - WHERE a.aggfnoid = p.oid - and p.proargtypes[0] = t.oid - ORDER BY proname, 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_opclass opc, pg_amop amop, pg_operator opr - WHERE opc.opcamid = am.oid - and amop.amopclaid = opc.oid - and amop.amopopr = opr.oid - ORDER BY amname, opcname, oprname; |