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.
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.
Attached is a copy of the Postgres support routines I wrote to allow me to more cleanly interface to the libpg library, more like a 4gl SQL interface. It has several features that may be useful for others: I have simplified the C code that calls libpq by wrapping all the functionality of libpq in calls to connectdb(), doquery(), fetch(), fetchwithnulls() and disconnectdb(). Each call returns a structure or value, so if you need to do more work with the result, you can. Also, I have a global variable that allows you to disable the error checking I have added to the doquery() routine. I have added a function called fetch(), which allows you to pass pointers as parameters, and on return the variables are filled with the data from the binary cursor you opened. These binary cursors are not useful if you are running the query engine on a system with a different architecture than the database server. If you pass a NULL pointer, the column is skipped, and you can use libpq to handle it as you wish. I have used sigprocmask() to block the reception of certain signals while the program is executing SQL queries. This prevents a user pressing Control-C from stopping all the back ends. It blocks SIGHUP, SIGINT, and SIGTERM, but does not block SIGQUIT or obviously kill -9. If your platform does not support sigprocmask(), you can remove those function calls. ( Am I correct that abnormal termination can cause shared memory resynchronization?) There is a demo program called pginsert that demonstrates how the library can be used. You can create a library of pginterface.c and halt.c, and just include pginterface.h in your source code. I am willing to maintain this if people find problems or want additional functionality. Bruce Momjian (root@candle.pha.pa.us)
DESCRIPTION:
~/postgres95/src/libpgtcl
------------
This is version 1.3 of pg95perl5.
Pg95perl5 is an interface between Larry Wall's language perl version 5 and the
database Postgres95. This has been done by using the Perl5 application program-
ming interface for C extensions which calls the Postgres programmer's interface
LIBQ. pg95perl5 trys to implement the LIBPQ-interface as close, as possible.
Please send comments and bug reports to
REQUIREMENTS:
-------------
- perl5.003
- Postgres95-1.08
PLATFORMS:
----------
This release of pg95perl5 has been developed using Linux 2.0 with
dynamic loading for the perl extensions. It also works using static
loading. Let me know, if there are any problems with other platforms.
INSTALLATION:
-------------
Using dynamic loading for perl extensions, the preferred method is to unpack
the tar file outside the perl source tree. This assumes, that you already
have installed perl5.
Make sure, that the locations of the postgres include-files and the libpq-
library in Makefile.PL are correct. Then
perl Makefile.PL
make
make install
If you choose static loading, the contents of this package is assumed to be
in the subdirectory ext/Pg in the perl source tree. Build perl5 as described
in the README of perl:
- when running Configure, add Pg to the list of static extensions.
- run make depend, make, make install.
TESTING:
--------
Run 'make test'.
Note, that the user running this script must have been created with
the access rights to create databases *AND* users ! Do not run this
script as root !
---------------------------------------------------------------------------
Edmund Mergl
---------------------------------------------------------------------------
libpgtcl
libpgtcl is a library that implements Tcl commands for front-end
clients to interact with the Postgres95 backend. See libpgtcl.doc for
details.
For an example of how to build a new tclsh to use libpgtcl, see the
directory ../bin/pgtclsh