From 668aa24fc495a142fdf2ac12b32d849a04b21dc0 Mon Sep 17 00:00:00 2001 From: "Marc G. Fournier" Date: Tue, 23 Jul 1996 03:13:58 +0000 Subject: [PATCH] More merge's from Dr. George's sourec tree --- src/extend/array/array_iterator.c | 251 ++++++++++++++ src/extend/array/array_iterator.doc | 26 ++ src/extend/array/array_iterator.sql | 137 ++++++++ src/extend/datetime/datetime_functions.c | 147 +++++++++ src/extend/datetime/datetime_functions.doc | 25 ++ src/extend/datetime/datetime_functions.sql | 69 ++++ src/extend/pginsert/Makefile | 19 ++ src/extend/pginsert/halt.c | 58 ++++ src/extend/pginsert/halt.h | 7 + src/extend/pginsert/pginsert.c | 98 ++++++ src/extend/pginsert/pginterface.c | 154 +++++++++ src/extend/pginsert/pginterface.h | 12 + src/extend/soundex/soundex.c | 83 +++++ src/extend/soundex/soundex.sql | 57 ++++ src/extend/string/string_io.c | 361 +++++++++++++++++++++ src/extend/string/string_io.sql | 111 +++++++ 16 files changed, 1615 insertions(+) create mode 100644 src/extend/array/array_iterator.c create mode 100644 src/extend/array/array_iterator.doc create mode 100644 src/extend/array/array_iterator.sql create mode 100644 src/extend/datetime/datetime_functions.c create mode 100644 src/extend/datetime/datetime_functions.doc create mode 100644 src/extend/datetime/datetime_functions.sql create mode 100644 src/extend/pginsert/Makefile create mode 100644 src/extend/pginsert/halt.c create mode 100644 src/extend/pginsert/halt.h create mode 100644 src/extend/pginsert/pginsert.c create mode 100644 src/extend/pginsert/pginterface.c create mode 100644 src/extend/pginsert/pginterface.h create mode 100644 src/extend/soundex/soundex.c create mode 100644 src/extend/soundex/soundex.sql create mode 100644 src/extend/string/string_io.c create mode 100644 src/extend/string/string_io.sql diff --git a/src/extend/array/array_iterator.c b/src/extend/array/array_iterator.c new file mode 100644 index 0000000000..95ab119f85 --- /dev/null +++ b/src/extend/array/array_iterator.c @@ -0,0 +1,251 @@ +/* + * array_iterator.c -- + * + * This file defines a new group of operators which take an + * array and a scalar value, iterate a scalar operator over the + * elements of the array and the value and compute a result as + * the logical OR or AND of the results. + * For example array_int4eq returns true if some of the elements + * of an array of int4 is equal to the given value: + * + * array_int4eq({1,2,3}, 1) --> true + * array_int4eq({1,2,3}, 4) --> false + * + * If we have defined T array types and O scalar operators + * we can define T x O array operators, each of them has a name + * like "array_" and takes an array of type T + * iterating the operator O over all the elements. Note however + * that some of the possible combination are invalid, for example + * the array_int4_like because there is no like operator for int4. + * It is now possible to write queries which look inside the arrays: + * + * create table t(id int4[], txt text[]); + * select * from t where t.id *= 123; + * select * from t where t.txt *~ '[a-z]'; + * select * from t where t.txt[1:3] **~ '[a-z]'; + * + * Copyright (c) 1996, Massimo Dal Zotto + */ + +#include +#include +#include +#include + +#include "postgres.h" +#include "pg_type.h" +#include "miscadmin.h" +#include "syscache.h" +#include "access/xact.h" +#include "utils/builtins.h" +#include "utils/elog.h" + +static int32 +array_iterator(Oid elemtype, Oid proc, int and, ArrayType *array, Datum value) +{ + HeapTuple typ_tuple; + TypeTupleForm typ_struct; + bool typbyval; + int typlen; + func_ptr proc_fn; + int pronargs; + int nitems, i, result; + int ndim, *dim; + char *p; + + /* Sanity checks */ + if ((array == (ArrayType *) NULL) + || (ARR_IS_LO(array) == true)) { + /* elog(NOTICE, "array_iterator: array is null"); */ + return (0); + } + ndim = ARR_NDIM(array); + dim = ARR_DIMS(array); + nitems = getNitems(ndim, dim); + if (nitems == 0) { + /* elog(NOTICE, "array_iterator: nitems = 0"); */ + return (0); + } + + /* Lookup element type information */ + typ_tuple = SearchSysCacheTuple(TYPOID, ObjectIdGetDatum(elemtype),0,0,0); + if (!HeapTupleIsValid(typ_tuple)) { + elog(WARN,"array_iterator: cache lookup failed for type %d", elemtype); + return 0; + } + typ_struct = (TypeTupleForm) GETSTRUCT(typ_tuple); + typlen = typ_struct->typlen; + typbyval = typ_struct->typbyval; + + /* Lookup the function entry point */ + proc_fn == (func_ptr) NULL; + fmgr_info(proc, &proc_fn, &pronargs); + if ((proc_fn == NULL) || (pronargs != 2)) { + elog(WARN, "array_iterator: fmgr_info lookup failed for oid %d", proc); + return (0); + } + + /* Scan the array and apply the operator to each element */ + result = 0; + p = ARR_DATA_PTR(array); + for (i = 0; i < nitems; i++) { + if (typbyval) { + switch(typlen) { + case 1: + result = (int) (*proc_fn)(*p, value); + break; + case 2: + result = (int) (*proc_fn)(* (int16 *) p, value); + break; + case 3: + case 4: + result = (int) (*proc_fn)(* (int32 *) p, value); + break; + } + p += typlen; + } else { + result = (int) (*proc_fn)(p, value); + if (typlen > 0) { + p += typlen; + } else { + p += INTALIGN(* (int32 *) p); + } + } + if (result) { + if (!and) { + return (1); + } + } else { + if (and) { + return (0); + } + } + } + + if (and && result) { + return (1); + } else { + return (0); + } +} + +/* + * Iterators for type _text + */ + +int32 +array_texteq(ArrayType *array, char* value) +{ + return array_iterator((Oid) 25, /* text */ + (Oid) 67, /* texteq */ + 0, /* logical or */ + array, (Datum)value); +} + +int32 +array_all_texteq(ArrayType *array, char* value) +{ + return array_iterator((Oid) 25, /* text */ + (Oid) 67, /* texteq */ + 1, /* logical and */ + array, (Datum)value); +} + +int32 +array_textregexeq(ArrayType *array, char* value) +{ + return array_iterator((Oid) 25, /* text */ + (Oid) 81, /* textregexeq */ + 0, /* logical or */ + array, (Datum)value); +} + +int32 +array_all_textregexeq(ArrayType *array, char* value) +{ + return array_iterator((Oid) 25, /* text */ + (Oid) 81, /* textregexeq */ + 1, /* logical and */ + array, (Datum)value); +} + +/* + * Iterators for type _char16. Note that the regexp operators + * take the second argument of type text. + */ + +int32 +array_char16eq(ArrayType *array, char* value) +{ + return array_iterator((Oid) 20, /* char16 */ + (Oid) 490, /* char16eq */ + 0, /* logical or */ + array, (Datum)value); +} + +int32 +array_all_char16eq(ArrayType *array, char* value) +{ + return array_iterator((Oid) 20, /* char16 */ + (Oid) 490, /* char16eq */ + 1, /* logical and */ + array, (Datum)value); +} + +int32 +array_char16regexeq(ArrayType *array, char* value) +{ + return array_iterator((Oid) 20, /* char16 */ + (Oid) 700, /* char16regexeq */ + 0, /* logical or */ + array, (Datum)value); +} + +int32 +array_all_char16regexeq(ArrayType *array, char* value) +{ + return array_iterator((Oid) 20, /* char16 */ + (Oid) 700, /* char16regexeq */ + 1, /* logical and */ + array, (Datum)value); +} + +/* + * Iterators for type _int4 + */ + +int32 +array_int4eq(ArrayType *array, int4 value) +{ + return array_iterator((Oid) 23, /* int4 */ + (Oid) 65, /* int4eq */ + 0, /* logical or */ + array, (Datum)value); +} + +int32 +array_all_int4eq(ArrayType *array, int4 value) +{ + return array_iterator((Oid) 23, /* int4 */ + (Oid) 65, /* int4eq */ + 1, /* logical and */ + array, (Datum)value); +} + +int32 +array_int4gt(ArrayType *array, int4 value) +{ + return array_iterator((Oid) 23, /* int4 */ + (Oid) 147, /* int4gt */ + 0, /* logical or */ + array, (Datum)value); +} + +int32 +array_all_int4gt(ArrayType *array, int4 value) +{ + return array_iterator((Oid) 23, /* int4 */ + (Oid) 147, /* int4gt */ + 1, /* logical and */ + array, (Datum)value); +} diff --git a/src/extend/array/array_iterator.doc b/src/extend/array/array_iterator.doc new file mode 100644 index 0000000000..01c1b2195c --- /dev/null +++ b/src/extend/array/array_iterator.doc @@ -0,0 +1,26 @@ +From: Massimo Dal Zotto +Date: Mon, 6 May 1996 01:03:37 +0200 (MET DST) +Subject: [PG95]: new operators for arrays + +- -----BEGIN PGP SIGNED MESSAGE----- + +Hi, + +I have written an extension to Postgres95 which allows to use qualification +clauses based on the values of single elements of arrays. +For example I can now select rows having some or all element of an array +attribute equal to a given value or matching a regular expression: + +select * from t where t.foo *= 'bar'; +select * from t where t.foo **~ '^ba[rz]'; + +The scheme is quite general, each operator which operates on a base type can +be iterated over the elements of an array. It seem to work well but defining +each new operators requires writing a different C function. Furthermore in +each function there are two hardcoded OIDs which reference a base type and +a procedure. Not very portable. Can anyone suggest a better and more portable +way to do it ? Do you think this could be a useful feature for next release ? +Here is my code, it can be compiled and loaded as a dynamic module without +need to recompile the backend. I have defined only the few operators I needed, +the list can be extended. Feddback is welcome. + diff --git a/src/extend/array/array_iterator.sql b/src/extend/array/array_iterator.sql new file mode 100644 index 0000000000..7a3356266c --- /dev/null +++ b/src/extend/array/array_iterator.sql @@ -0,0 +1,137 @@ +/* + * SQL code + +- - -- load the new functions +- - -- +load '/home/dz/lib/postgres/array_iterator.so'; + +- - -- define the array operators *=, **=, *~ and **~ for type _text +- - -- +create function array_texteq(_text, text) + returns bool + as '/home/dz/lib/postgres/array_iterator.so' + language 'c'; + +create function array_all_texteq(_text, text) + returns bool + as '/home/dz/lib/postgres/array_iterator.so' + language 'c'; + +create function array_textregexeq(_text, text) + returns bool + as '/home/dz/lib/postgres/array_iterator.so' + language 'c'; + +create function array_all_textregexeq(_text, text) + returns bool + as '/home/dz/lib/postgres/array_iterator.so' + language 'c'; + +create operator *= ( + leftarg=_text, + rightarg=text, + procedure=array_texteq); + +create operator **= ( + leftarg=_text, + rightarg=text, + procedure=array_all_texteq); + +create operator *~ ( + leftarg=_text, + rightarg=text, + procedure=array_textregexeq); + +create operator **~ ( + leftarg=_text, + rightarg=text, + procedure=array_all_textregexeq); + +- - -- define the array operators *=, **=, *~ and **~ for type _char16 +- - -- +create function array_char16eq(_char16, char16) + returns bool + as '/home/dz/lib/postgres/array_iterator.so' + language 'c'; + +create function array_all_char16eq(_char16, char16) + returns bool + as '/home/dz/lib/postgres/array_iterator.so' + language 'c'; + +create function array_char16regexeq(_char16, text) + returns bool + as '/home/dz/lib/postgres/array_iterator.so' + language 'c'; + +create function array_all_char16regexeq(_char16, text) + returns bool + as '/home/dz/lib/postgres/array_iterator.so' + language 'c'; + +create operator *= ( + leftarg=_char16, + rightarg=char16, + procedure=array_char16eq); + +create operator **= ( + leftarg=_char16, + rightarg=char16, + procedure=array_all_char16eq); + +create operator *~ ( + leftarg=_char16, + rightarg=text, + procedure=array_char16regexeq); + +create operator **~ ( + leftarg=_char16, + rightarg=text, + procedure=array_all_char16regexeq); + +- - -- define the array operators *=, **=, *> and **> for type _int4 +- - -- +create function array_int4eq(_int4, int4) + returns bool + as '/home/dz/lib/postgres/array_iterator.so' + language 'c'; + +create function array_all_int4eq(_int4, int4) + returns bool + as '/home/dz/lib/postgres/array_iterator.so' + language 'c'; + +create function array_int4gt(_int4, int4) + returns bool + as '/home/dz/lib/postgres/array_iterator.so' + language 'c'; + +create function array_all_int4gt(_int4, int4) + returns bool + as '/home/dz/lib/postgres/array_iterator.so' + language 'c'; + +create operator *= ( + leftarg=_int4, + rightarg=int4, + procedure=array_int4eq); + +create operator **= ( + leftarg=_int4, + rightarg=int4, + procedure=array_all_int4eq); + +create operator *> ( + leftarg=_int4, + rightarg=int4, + procedure=array_int4gt); + +create operator **> ( + leftarg=_int4, + rightarg=int4, + procedure=array_all_int4gt); + +*/ + +/* end of file */ + diff --git a/src/extend/datetime/datetime_functions.c b/src/extend/datetime/datetime_functions.c new file mode 100644 index 0000000000..dc1fec8bd2 --- /dev/null +++ b/src/extend/datetime/datetime_functions.c @@ -0,0 +1,147 @@ +/* + * datetime_functions.c -- + * + * This file defines new functions for the time and date data types. + * + * Copyright (c) 1996, Massimo Dal Zotto + */ + +#include + +#include "postgres.h" +#include "pg_type.h" +#include "utils/palloc.h" + +typedef struct DateADT { + char day; + char month; + short year; +} DateADT; + +typedef struct TimeADT { + short hr; + short min; + float sec; +} TimeADT; + +TimeADT * +time_difference(TimeADT *time1, TimeADT *time2) +{ + TimeADT *time = (TimeADT*)palloc(sizeof(TimeADT)); + + time->sec = time1->sec - time2->sec; + time->min = time1->min - time2->min; + time->hr = time1->hr - time2->hr; + + if (time->sec < 0) { + time->sec += 60.0; + time->min--; + } else if (time->sec >= 60.0) { + time->sec -= 60.0; + time->min++; + } + + if (time->min < 0) { + time->min += 60; + time->hr--; + } else if (time->min >= 60) { + time->min -= 60; + time->hr++; + } + + if (time->hr < 0) { + time->hr += 24; + } else if (time->hr >= 24) { + time->hr -= 24; + } + + return (time); +} + +TimeADT * +currentTime() +{ + time_t current_time; + struct tm *tm; + TimeADT *result = (TimeADT*)palloc(sizeof(TimeADT)); + + current_time = time(NULL); + tm = localtime(¤t_time); + result->sec = tm->tm_sec; + result->min = tm->tm_min; + result->hr = tm->tm_hour; + + return (result); +} + +int4 +currentDate() +{ + time_t current_time; + struct tm *tm; + int4 result; + DateADT *date = (DateADT*)&result; + + current_time = time(NULL); + tm = localtime(¤t_time); + date->day = tm->tm_mday; + date->month = tm->tm_mon+1; + date->year = tm->tm_year+1900; + + return (result); +} + +int4 +hours(TimeADT *time) +{ + return (time->hr); +} + +int4 +minutes(TimeADT *time) +{ + return (time->min); +} + +int4 +seconds(TimeADT *time) +{ + int seconds = (int)time->sec; + return (seconds); +} + +int4 +day(int4 val) +{ + DateADT *date = (DateADT*)&val; + return (date->day); +} + +int4 +month(int4 val) +{ + DateADT *date = (DateADT*)&val; + return (date->month); +} + +int4 +year(int4 val) +{ + DateADT *date = (DateADT*)&val; + return (date->year); +} + +int4 +asMinutes(TimeADT *time) +{ + int seconds = (int)time->sec; + return (time->min + 60*time->hr); +} + +int4 +asSeconds(TimeADT *time) +{ + int seconds = (int)time->sec; + return (seconds + 60*time->min + 3600*time->hr); +} + diff --git a/src/extend/datetime/datetime_functions.doc b/src/extend/datetime/datetime_functions.doc new file mode 100644 index 0000000000..0c7a01819a --- /dev/null +++ b/src/extend/datetime/datetime_functions.doc @@ -0,0 +1,25 @@ +From: Massimo Dal Zotto +Date: Tue, 14 May 1996 14:31:18 +0200 (MET DST) +Subject: [PG95]: new postgres functions + +- -----BEGIN PGP SIGNED MESSAGE----- + +Some time ago I read in the mailing list requests of people looking +for more time and date functions. I have now written some of them: + + time_difference(time1, time2) ,also defined as operator '-' + hour(time) + minutes(time) + seconds(time) + asMinutes(time) + asSeconds(time) + currentTime() + currentDate() + +The file can be compiled as shared library and loaded as dynamic module +without need to recompile the backend. This can also be taken as an example +of the extensibility of postgres (user-defined functions, operators, etc). +I would be nice to see more of these user contributed modules posted to this +list and hopefully accessible from the Postgres home page. + + diff --git a/src/extend/datetime/datetime_functions.sql b/src/extend/datetime/datetime_functions.sql new file mode 100644 index 0000000000..2e50a4a19f --- /dev/null +++ b/src/extend/datetime/datetime_functions.sql @@ -0,0 +1,69 @@ + +-- SQL code to load and define 'datetime' functions + +-- load the new functions + +load '/home/dz/lib/postgres/datetime_functions.so'; + +-- define the new functions in postgres + +create function time_difference(time,time) + returns time + as '/home/dz/lib/postgres/datetime_functions.so' + language 'c'; + +create function currentDate() + returns date + as '/home/dz/lib/postgres/datetime_functions.so' + language 'c'; + +create function currentTime() + returns time + as '/home/dz/lib/postgres/datetime_functions.so' + language 'c'; + +create function hours(time) + returns int4 + as '/home/dz/lib/postgres/datetime_functions.so' + language 'c'; + +create function minutes(time) + returns int4 + as '/home/dz/lib/postgres/datetime_functions.so' + language 'c'; + +create function seconds(time) + returns int4 + as '/home/dz/lib/postgres/datetime_functions.so' + language 'c'; + +create function day(date) + returns int4 + as '/home/dz/lib/postgres/datetime_functions.so' + language 'c'; + +create function month(date) + returns int4 + as '/home/dz/lib/postgres/datetime_functions.so' + language 'c'; + +create function year(date) + returns int4 + as '/home/dz/lib/postgres/datetime_functions.so' + language 'c'; + +create function asMinutes(time) + returns int4 + as '/home/dz/lib/postgres/datetime_functions.so' + language 'c'; + +create function asSeconds(time) + returns int4 + as '/home/dz/lib/postgres/datetime_functions.so' + language 'c'; + +create operator - ( + leftarg=time, + rightarg=time, + procedure=time_difference); + diff --git a/src/extend/pginsert/Makefile b/src/extend/pginsert/Makefile new file mode 100644 index 0000000000..7759ef8d28 --- /dev/null +++ b/src/extend/pginsert/Makefile @@ -0,0 +1,19 @@ +# +# Makefile +# +# +TARGET = pginsert +CFLAGS = -g -Wall -I/u/postgres95/include +LIBS = -L/u/postgres95/lib -lpq + +$(TARGET) : pginsert.o pginterface.o halt.o + $(CC) -o $(TARGET) $(XFLAGS) $(CFLAGS) \ + pginsert.o pginterface.o halt.o $(LIBS) + +clean: + rm -f *.o $(TARGET) log core + +install: + make clean + make CFLAGS=-O + install -s -o bin -g bin $(TARGET) /usr/local/bin diff --git a/src/extend/pginsert/halt.c b/src/extend/pginsert/halt.c new file mode 100644 index 0000000000..58ca11a587 --- /dev/null +++ b/src/extend/pginsert/halt.c @@ -0,0 +1,58 @@ +/* +** +** halt.c +** +** This is used to print out error messages and exit +*/ + +#include +#include +#include +#include +#include +#include + + +/*------------------------------------------------------------------------- +** +** halt - print error message, and call clean up routine or exit +** +**------------------------------------------------------------------------*/ + +/*VARARGS*/ +void halt(va_alist) +va_dcl +{ + va_list arg_ptr; + char *format, *pstr; + void (*sig_func)(); + + va_start(arg_ptr); + format = va_arg(arg_ptr,char *); + if (strncmp(format,"PERROR", 6) != 0) + vfprintf(stderr,format,arg_ptr); + else + { + for (pstr=format+6; *pstr == ' ' || *pstr == ':'; pstr++) + ; + vfprintf(stderr,pstr,arg_ptr); + perror(""); + } + va_end(arg_ptr); + fflush(stderr); + + /* call one clean up function if defined */ + if ( (sig_func = signal(SIGTERM, SIG_DFL)) != SIG_DFL && + sig_func != SIG_IGN) + (*sig_func)(0); + else if ( (sig_func = signal(SIGHUP, SIG_DFL)) != SIG_DFL && + sig_func != SIG_IGN) + (*sig_func)(0); + else if ( (sig_func = signal(SIGINT, SIG_DFL)) != SIG_DFL && + sig_func != SIG_IGN) + (*sig_func)(0); + else if ( (sig_func = signal(SIGQUIT, SIG_DFL)) != SIG_DFL && + sig_func != SIG_IGN) + (*sig_func)(0); + exit(1); +} diff --git a/src/extend/pginsert/halt.h b/src/extend/pginsert/halt.h new file mode 100644 index 0000000000..cb4ea545b9 --- /dev/null +++ b/src/extend/pginsert/halt.h @@ -0,0 +1,7 @@ +/* +** halt.h +** +*/ + +void halt(); + diff --git a/src/extend/pginsert/pginsert.c b/src/extend/pginsert/pginsert.c new file mode 100644 index 0000000000..eb732c6930 --- /dev/null +++ b/src/extend/pginsert/pginsert.c @@ -0,0 +1,98 @@ +/* + * insert.c + * +*/ + +#include +#include +#include +#include +#include +#include "pginterface.h" + +int main(int argc, char **argv) +{ + char query[4000]; + int row =1; + int aint; + float afloat; + double adouble; + char achar[11], achar16[17], abpchar[11], avarchar[51], atext[51]; + time_t aabstime; + + if (argc != 2) + halt("Usage: %s database\n",argv[0]); + + connectdb(argv[1],NULL,NULL,NULL,NULL); + + skip_query_errors = 1; + doquery("DROP TABLE testfetch"); + skip_query_errors = 0; + + doquery("\ + CREATE TABLE testfetch( \ + aint int4, \ + afloat float4, \ + adouble float8, \ + achar char, \ + achar16 char16, \ + abpchar char(10), \ + avarchar varchar(50), \ + atext text, \ + aabstime abstime) \ + "); + + while(1) + { + sprintf(query,"INSERT INTO testfetch VALUES ( \ + %d, \ + 2322.12, \ + '923121.0323'::float8, \ + 'A', \ + 'Betty', \ + 'Charley', \ + 'Doug', \ + 'Ernie', \ + 'now' )", row); + doquery(query); + + doquery("BEGIN WORK"); + doquery("DECLARE c_testfetch BINARY CURSOR FOR \ + SELECT * FROM testfetch"); + + doquery("FETCH ALL IN c_testfetch"); + + while (fetch( + &aint, + &afloat, + &adouble, + achar, + achar16, + abpchar, + avarchar, + atext, + &aabstime) != END_OF_TUPLES) + printf("int %d\nfloat %f\ndouble %f\nchar %s\nchar16 %s\n\ +bpchar %s\nvarchar %s\ntext %s\nabstime %s", + aint, + afloat, + adouble, + achar, + achar16, + abpchar, + avarchar, + atext, + ctime(&aabstime)); + + + doquery("CLOSE c_testfetch"); + doquery("COMMIT WORK"); + printf("--- %-d rows inserted so far\n",row); + + row++; + } + + disconnectdb(); + return 0; +} + diff --git a/src/extend/pginsert/pginterface.c b/src/extend/pginsert/pginterface.c new file mode 100644 index 0000000000..3b5ddf05f2 --- /dev/null +++ b/src/extend/pginsert/pginterface.c @@ -0,0 +1,154 @@ +/* + * pginterface.c + * +*/ + +#include +#include +#include +#include + +#include +#include +#include "pginterface.h" + +static void sig_disconnect(); +static void set_signals(); + +#define NUL '\0' + +/* GLOBAL VARIABLES */ +static PGconn* conn; +static PGresult* res = NULL; +int skip_query_errors = 0; + +/* LOCAL VARIABLES */ +static sigset_t block_sigs, unblock_sigs; +static int tuple; + +/* +** +** connectdb - returns PGconn structure +** +*/ +PGconn *connectdb( char *dbName, + char *pghost, + char *pgport, + char *pgoptions, + char *pgtty) +{ + /* make a connection to the database */ + conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName); + if (PQstatus(conn) == CONNECTION_BAD) + halt("Connection to database '%s' failed.\n%s\n", dbName, + PQerrorMessage(conn)); + set_signals(); + return conn; +} + +/* +** +** disconnectdb +** +*/ +void disconnectdb() +{ + PQfinish(conn); +} + +/* +** +** doquery - returns PGresult structure +** +*/ +PGresult *doquery(char *query) +{ + if (res != NULL) + PQclear(res); + + sigprocmask(SIG_SETMASK,&block_sigs,NULL); + res = PQexec(conn, query); + sigprocmask(SIG_SETMASK,&unblock_sigs,NULL); + + if (skip_query_errors == 0 && + (res == NULL || + PQresultStatus(res) == PGRES_BAD_RESPONSE || + PQresultStatus(res) == PGRES_NONFATAL_ERROR || + PQresultStatus(res) == PGRES_FATAL_ERROR)) + { + if (res != NULL) + fprintf(stderr,"query error: %s\n",PQcmdStatus(res)); + else fprintf(stderr,"connection error: %s\n",PQerrorMessage(conn)); + PQfinish(conn); + halt("failed request: %s\n", query); + } + tuple = 0; + return res; +} + +/* +** +** fetch - returns tuple number (starts at 0), or the value END_OF_TUPLES +** NULL pointers are skipped +** +*/ +int fetch(void *param, ...) +{ + va_list ap; + int arg, num_args; + + num_args = PQnfields(res); + + if (tuple >= PQntuples(res)) + return END_OF_TUPLES; + va_start(ap, param); + for (arg = 0; arg < num_args; arg++) + { + if (param != NULL) + { + if (PQfsize(res, arg) == -1) + { + memcpy(param,PQgetvalue(res,tuple,arg),PQgetlength(res,tuple,arg)); + ((char *)param)[PQgetlength(res,tuple,arg)] = NUL; + } + else + memcpy(param,PQgetvalue(res,tuple,arg),PQfsize(res,arg)); + } + param = va_arg(ap, char *); + } + va_end(ap); + return tuple++; +} + +/* +** +** sig_disconnect +** +*/ +static void sig_disconnect() +{ + fprintf(stderr,"exiting...\n"); + PQfinish(conn); + exit(1); +} + +/* +** +** set_signals +** +*/ +static void set_signals() +{ + sigemptyset(&block_sigs); + sigemptyset(&unblock_sigs); + sigaddset(&block_sigs,SIGTERM); + sigaddset(&block_sigs,SIGHUP); + sigaddset(&block_sigs,SIGINT); +/* sigaddset(&block_sigs,SIGQUIT); no block */ + sigprocmask(SIG_SETMASK,&unblock_sigs,NULL); + signal(SIGTERM,sig_disconnect); + signal(SIGHUP,sig_disconnect); + signal(SIGINT,sig_disconnect); + signal(SIGQUIT,sig_disconnect); +} + diff --git a/src/extend/pginsert/pginterface.h b/src/extend/pginsert/pginterface.h new file mode 100644 index 0000000000..a84d519411 --- /dev/null +++ b/src/extend/pginsert/pginterface.h @@ -0,0 +1,12 @@ +/* + * pglib.h + * +*/ + +PGresult *doquery(char *query); +PGconn *connectdb(); +void disconnectdb(); +int fetch(void *param, ...); +int skip_query_errors; + +#define END_OF_TUPLES (-1) diff --git a/src/extend/soundex/soundex.c b/src/extend/soundex/soundex.c new file mode 100644 index 0000000000..2ce6ef510f --- /dev/null +++ b/src/extend/soundex/soundex.c @@ -0,0 +1,83 @@ +/*****************************************************************************/ +/* soundex.c */ +/*****************************************************************************/ + +#include +#include +#include "postgres.h" /* for char16, etc. */ +#include "utils/palloc.h" /* for palloc */ +#include "libpq-fe.h" /* for TUPLE */ +#include +#include + +/* prototype for soundex function */ +char *soundex(char *instr, char *outstr); + +text *text_soundex(text *t) +{ + /* ABCDEFGHIJKLMNOPQRSTUVWXYZ */ + char *table = "01230120022455012623010202"; + int count = 0; + text *new_t; + + char outstr[6+1]; /* max length of soundex is 6 */ + char *instr; + + /* make a null-terminated string */ + instr=palloc(VARSIZE(t)+1); + memcpy(instr,VARDATA(t),VARSIZE(t)-VARHDRSZ); + instr[VARSIZE(t)-VARHDRSZ] = (char)0; + + /* load soundex into outstr */ + soundex(instr, outstr); + + /* Now the outstr contains the soundex of instr */ + /* copy outstr to new_t */ + new_t = (text *) palloc(strlen(outstr)+VARHDRSZ); + memset(new_t, 0, strlen(outstr)+1); + VARSIZE(new_t) = strlen(outstr)+VARHDRSZ; + memcpy((void *) VARDATA(new_t), + (void *) outstr, + strlen(outstr)); + + /* free instr */ + pfree(instr); + + return(new_t); +} + +char *soundex(char *instr, char *outstr) +{ /* ABCDEFGHIJKLMNOPQRSTUVWXYZ */ + char *table = "01230120022455012623010202"; + int count = 0; + + while(!isalpha(instr[0]) && instr[0]) + ++instr; + + if(!instr[0]) { /* Hey! Where'd the string go? */ + outstr[0]=(char)0; + return outstr; + } + + if(toupper(instr[0]) == 'P' && toupper(instr[1]) == 'H') { + instr[0] = 'F'; + instr[1] = 'A'; + } + + *outstr++ = (char)toupper(*instr++); + + while(*instr && count < 5) { + if(isalpha(*instr) && *instr != *(instr-1)) { + *outstr = table[toupper(instr[0]) - 'A']; + if(*outstr != '0') { + ++outstr; + ++count; + } + } + ++instr; + } + + *outstr = '\0'; + return(outstr); +} + diff --git a/src/extend/soundex/soundex.sql b/src/extend/soundex/soundex.sql new file mode 100644 index 0000000000..af8ea41fd2 --- /dev/null +++ b/src/extend/soundex/soundex.sql @@ -0,0 +1,57 @@ +--------------- soundex.sql: + +CREATE FUNCTION text_soundex(text) RETURNS text + AS '/usr/local/postgres/postgres95/src/funcs/soundex.so' LANGUAGE 'c'; + +SELECT text_soundex('hello world!'); + +CREATE TABLE s (nm text)\g + +insert into s values ('john')\g +insert into s values ('joan')\g +insert into s values ('wobbly')\g + +select * from s +where text_soundex(nm) = text_soundex('john')\g + +select nm from s a, s b +where text_soundex(a.nm) = text_soundex(b.nm) +and a.oid <> b.oid\g + +CREATE FUNCTION text_sx_eq(text, text) RETURNS bool AS +'select text_soundex($1) = text_soundex($2)' +LANGUAGE 'sql'\g + +CREATE FUNCTION text_sx_lt(text,text) RETURNS bool AS +'select text_soundex($1) < text_soundex($2)' +LANGUAGE 'sql'\g + +CREATE FUNCTION text_sx_gt(text,text) RETURNS bool AS +'select text_soundex($1) > text_soundex($2)' +LANGUAGE 'sql'; + +CREATE FUNCTION text_sx_le(text,text) RETURNS bool AS +'select text_soundex($1) <= text_soundex($2)' +LANGUAGE 'sql'; + +CREATE FUNCTION text_sx_ge(text,text) RETURNS bool AS +'select text_soundex($1) >= text_soundex($2)' +LANGUAGE 'sql'; + +CREATE FUNCTION text_sx_ne(text,text) RETURNS bool AS +'select text_soundex($1) <> text_soundex($2)' +LANGUAGE 'sql'; + +DROP OPERATOR #= (text,text)\g + +CREATE OPERATOR #= (leftarg=text, rightarg=text, procedure=text_sx_eq, +commutator=text_sx_eq)\g + +SELECT * +FROM s +WHERE text_sx_eq(nm,'john')\g + +SELECT * +from s +where s.nm #= 'john'; + diff --git a/src/extend/string/string_io.c b/src/extend/string/string_io.c new file mode 100644 index 0000000000..ab49c5321a --- /dev/null +++ b/src/extend/string/string_io.c @@ -0,0 +1,361 @@ +/* + * string_io.c -- + * + * This file defines new input/output conversion routines for strings. + * + * Copyright (c) 1996, Massimo Dal Zotto + */ + +#include +#include + +#include "postgres.h" +#include "utils/elog.h" +#include "utils/palloc.h" +#include "utils/builtins.h" + +/* define this if you want to see iso-8859 characters */ +#define ISO8859 + +#define MIN(x, y) ((x) < (y) ? (x) : (y)) +#define VALUE(char) ((char) - '0') +#define DIGIT(val) ((val) + '0') +#define ISOCTAL(c) (((c) >= '0') && ((c) <= '7')) +#ifndef ISO8859 +#define NOTPRINTABLE(c) (!isprint(c)) +#else +#define NOTPRINTABLE(c) (!isprint(c) && ((c) < 0xa0)) +#endif + +/* + * string_output() -- + * + * This function takes a pointer to a string data and an optional + * data size and returns a printable representation of the data + * translating all escape sequences to C-like \nnn or \c escapes. + * The function is used by output methods of various string types. + * + * Arguments: + * data - input data (can be NULL) + * size - optional size of data. A negative value indicates + * that data is a null terminated string. + * + * Returns: + * a pointer to a new string containing the printable + * representation of data. + */ + +char * +string_output(char *data, int size) +{ + register unsigned char c, *p, *r, *result; + register int l, len; + + if (data == NULL) { + result = (char *) palloc(2); + result[0] = '-'; + result[1] = '\0'; + return (result); + } + + if (size < 0) { + size = strlen(data); + } + + /* adjust string length for escapes */ + len = size; + for (p=data,l=size; l>0; p++,l--) { + switch (*p) { + case '\\': + case '"' : + case '{': + case '}': + case '\b': + case '\f': + case '\n': + case '\r': + case '\t': + case '\v': + len++; + break; + default: + if (NOTPRINTABLE(c)) { + len += 3; + } + } + } + len++; + + result = (char *) palloc(len); + + for (p=data,r=result,l=size; (l > 0) && (c = *p); p++,l--) { + switch (c) { + case '\\': + case '"' : + case '{': + case '}': + *r++ = '\\'; + *r++ = c; + break; + case '\b': + *r++ = '\\'; + *r++ = 'b'; + break; + case '\f': + *r++ = '\\'; + *r++ = 'f'; + break; + case '\n': + *r++ = '\\'; + *r++ = 'n'; + break; + case '\r': + *r++ = '\\'; + *r++ = 'r'; + break; + case '\t': + *r++ = '\\'; + *r++ = 't'; + break; + case '\v': + *r++ = '\\'; + *r++ = 'v'; + break; + default: + if (NOTPRINTABLE(c)) { + *r = '\\'; + r += 3; + *r-- = DIGIT(c & 07); + c >>= 3; + *r-- = DIGIT(c & 07); + c >>= 3; + *r = DIGIT(c & 03); + r += 3; + } else { + *r++ = c; + } + } + } + *r = '\0'; + + return((char *) result); +} + +/* + * string_input() -- + * + * This function accepts a C string in input and copies it into a new + * object allocated with palloc() translating all escape sequences. + * An optional header can be allocatd before the string, for example + * to hold the length of a varlena object. + * This function is not necessary for input from sql commands because + * the parser already does escape translation, all data input routines + * receive strings in internal form. + * + * Arguments: + * str - input string possibly with escapes + * size - the required size of new data. A value of 0 + * indicates a variable size string, while a + * negative value indicates a variable size string + * of size not greater than this absolute value. + * hdrsize - size of an optional header to be allocated before + * the data. It must then be filled by the caller. + * rtn_size - an optional pointer to an int variable where the + * size of the new string is stored back. + * + * Returns: + * a pointer to the new string or the header. + */ + +char * +string_input(char *str, int size, int hdrsize, int *rtn_size) +{ + register unsigned char *p, *r; + unsigned char *result; + int len; + + if ((str == NULL) || (hdrsize < 0)) { + return (char *) NULL; + } + + /* Compute result size */ + len = strlen(str); + for (p=str; *p; ) { + if (*p++ == '\\') { + if (ISOCTAL(*p)) { + if (ISOCTAL(*(p+1))) { + p++; + len--; + } + if (ISOCTAL(*(p+1))) { + p++; + len--; + } + } + if (*p) p++; + len--; + } + } + + /* result has variable length */ + if (size == 0) { + size = len+1; + } else + + /* result has variable length with maximum size */ + if (size < 0) { + size = MIN(len, - size)+1; + } + + result = (char *) palloc(hdrsize+size); + memset(result, 0, hdrsize+size); + if (rtn_size) { + *rtn_size = size; + } + + r = result + hdrsize; + for (p=str; *p; ) { + register unsigned char c; + if ((c = *p++) == '\\') { + switch (c = *p++) { + case '\0': + p--; + break; + case '0': + case '1': + case '2': + case '3': + case '4': + case '5': + case '6': + case '7': + c = VALUE(c); + if (isdigit(*p)) { + c = (c<<3) + VALUE(*p++); + } + if (isdigit(*p)) { + c = (c<<3) + VALUE(*p++); + } + *r++ = c; + break; + case 'b': + *r++ = '\b'; + break; + case 'f': + *r++ = '\f'; + break; + case 'n': + *r++ = '\n'; + break; + case 'r': + *r++ = '\r'; + break; + case 't': + *r++ = '\t'; + break; + case 'v': + *r++ = '\v'; + break; + default: + *r++ = c; + } + } else { + *r++ = c; + } + } + + return((char *) result); +} + +char * +c_charout(int32 c) +{ + char str[2]; + + str[0] = (char) c; + str[1] = '\0'; + + return (string_output(str, 1)); +} + +char * +c_char2out(uint16 s) +{ + return (string_output((char *) &s, 2)); +} + +char * +c_char4out(uint32 s) +{ + return (string_output((char *) &s, 4)); +} + +char * +c_char8out(char *s) +{ + return (string_output(s, 8)); +} + +char * +c_char16out(char *s) +{ + return (string_output(s, 16)); +} + +/* + * This can be used for text, bytea, SET and unknown data types + */ + +char * +c_textout(struct varlena *vlena) +{ + int len = 0; + char *s = NULL; + + if (vlena) { + len = VARSIZE(vlena) - VARHDRSZ; + s = VARDATA(vlena); + } + return (string_output(s, len)); +} + +/* + * This can be used for varchar and bpchar strings + */ + +char * +c_varcharout(char *s) +{ + int len; + + if (s) { + len = *(int32*)s - 4; + s += 4; + } + return (string_output(s, len)); +} + +#ifdef 0 +struct varlena * +c_textin(char *str) +{ + struct varlena *result; + int len; + + if (str == NULL) { + return ((struct varlena *) NULL); + } + + result = (struct varlena *) string_input(str, 0, VARHDRSZ, &len); + VARSIZE(result) = len; + + return (result); +} + +char * +c_char16in(char *str) +{ + return (string_input(str, 16, 0, NULL)); +} +#endif + diff --git a/src/extend/string/string_io.sql b/src/extend/string/string_io.sql new file mode 100644 index 0000000000..011371707d --- /dev/null +++ b/src/extend/string/string_io.sql @@ -0,0 +1,111 @@ + +- - -- load the new functions +- - -- +load '/home/dz/lib/postgres/string_output.so'; + +- - -- create function c_textin(opaque) +- - -- returns text +- - -- as '/home/dz/lib/postgres/string_output.so' +- - -- language 'c'; + +create function c_charout(opaque) + returns int4 + as '/home/dz/lib/postgres/string_output.so' + language 'c'; + +create function c_char2out(opaque) + returns int4 + as '/home/dz/lib/postgres/string_output.so' + language 'c'; + +create function c_char4out(opaque) + returns int4 + as '/home/dz/lib/postgres/string_output.so' + language 'c'; + +create function c_char8out(opaque) + returns int4 + as '/home/dz/lib/postgres/string_output.so' + language 'c'; + +create function c_char16out(opaque) + returns int4 + as '/home/dz/lib/postgres/string_output.so' + language 'c'; + +create function c_textout(opaque) + returns int4 + as '/home/dz/lib/postgres/string_output.so' + language 'c'; + +create function c_varcharout(opaque) + returns int4 + as '/home/dz/lib/postgres/string_output.so' + language 'c'; + +- - -- define a function which sets the new output routines for char types +- - -- +- - -- select c_mode(); +- - -- +create function c_mode() + returns text + as 'update pg_type set typoutput=''c_charout'' where typname=''char''\; + update pg_type set typoutput=''c_char2out'' where typname=''char2''\; + update pg_type set typoutput=''c_char4out'' where typname=''char4''\; + update pg_type set typoutput=''c_char8out'' where typname=''char8''\; + update pg_type set typoutput=''c_char16out'' where typname=''char16''\; + update pg_type set typoutput=''c_textout'' where typname=''text''\; + update pg_type set typoutput=''c_textout'' where typname=''bytea''\; + update pg_type set typoutput=''c_textout'' where typname=''unknown''\; + update pg_type set typoutput=''c_textout'' where typname=''SET''\; + update pg_type set typoutput=''c_varcharout'' where typname=''varchar''\; + update pg_type set typoutput=''c_varcharout'' where typname=''bpchar''\; + select ''c_mode''::text' + language 'sql'; + +- - -- define a function which restores the original routines for char types +- - -- +- - -- select pg_mode(); +- - -- +create function pg_mode() + returns text + as 'update pg_type set typoutput=''charout'' where typname=''char''\; + update pg_type set typoutput=''char2out'' where typname=''char2''\; + update pg_type set typoutput=''char4out'' where typname=''char4''\; + update pg_type set typoutput=''char8out'' where typname=''char8''\; + update pg_type set typoutput=''char16out'' where typname=''char16''\; + update pg_type set typoutput=''textout'' where typname=''text''\; + update pg_type set typoutput=''textout'' where typname=''bytea''\; + update pg_type set typoutput=''textout'' where typname=''unknown''\; + update pg_type set typoutput=''textout'' where typname=''SET''\; + update pg_type set typoutput=''varcharout'' where typname=''varchar''\; + update pg_type set typoutput=''varcharout'' where typname=''bpchar''\; + select ''pg_mode''::text' + language 'sql'; + +- - -- or do the changes manually +- - -- +- - -- update pg_type set typoutput='charout' where typname='char'; +- - -- update pg_type set typoutput='char2out' where typname='char2'; +- - -- update pg_type set typoutput='char4out' where typname='char4'; +- - -- update pg_type set typoutput='char8out' where typname='char8'; +- - -- update pg_type set typoutput='char16out' where typname='char16'; +- - -- update pg_type set typoutput='textout' where typname='text'; +- - -- update pg_type set typoutput='textout' where typname='bytea'; +- - -- update pg_type set typoutput='textout' where typname='unknown'; +- - -- update pg_type set typoutput='textout' where typname='SET'; +- - -- update pg_type set typoutput='varcharout' where typname='varchar'; +- - -- update pg_type set typoutput='varcharout' where typname='bpchar'; +- - -- +- - -- update pg_type set typoutput='c_charout' where typname='char'; +- - -- update pg_type set typoutput='c_char2out' where typname='char2'; +- - -- update pg_type set typoutput='c_char4out' where typname='char4'; +- - -- update pg_type set typoutput='c_char8out' where typname='char8'; +- - -- update pg_type set typoutput='c_char16out' where typname='char16'; +- - -- update pg_type set typoutput='c_textout' where typname='text'; +- - -- update pg_type set typoutput='c_textout' where typname='bytea'; +- - -- update pg_type set typoutput='c_textout' where typname='unknown'; +- - -- update pg_type set typoutput='c_textout' where typname='SET'; +- - -- update pg_type set typoutput='c_varcharout' where typname='varchar'; +- - -- update pg_type set typoutput='c_varcharout' where typname='bpchar'; + -- 2.40.0