There are a lot of functions in PostgreSQL and for the most part, they are nicely tucked away in the pg_catalog schema so they don't get mixed with your functions.
There are a lot of contrib modules though and if you use a few of the big ones and just dump them in the public schema, the function list gets overwhelming and you have a hard time finding your own functions.
To keep our sanity we tend to create a schema called util or something like that where we stuff our own personal functions for easy navigation and for larger contribs, we may put these in a
separate schema altogether. Every once in a while, we screw up and put the functions in the wrong schema. Deleting these can become painful if there are a lot to delete.
Problem: How do you delete a butt load of functions without working up a sweat?
Use the below code cautiously. We start off with our general hack of writing an sql statement to write lots of sql statements. We had
hoped to use the more generic information_schema for this exercise, but the closest table we could find information_schema.routines, lists the names of the functions
but not the arguments. You need the arguments in your drop since PostgreSQL supports function argument overloading.
SELECT'DROPFUNCTION'|| ns.nspname ||'.'|| proname ||'('||oidvectortypes(proargtypes) ||');'FROM pg_proc INNERJOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE ns.nspname ='my_messed_up_schema'orderby proname;
The above will generate but not execute code that looks something like below so you can inspect the drops before executing:
We've been playing around with the snapshot builds of PgAdmin III 1.9 and would like to summarize some
of the new nice features added. PgAdmin III 1.9 has not been released yet, but has a couple of neat features brewing.
OpenJump is a Java Based, Cross-Platform open source GIS analysis and query tool. We've been using it a lot lately, and I would
say out of all the open source tools (and even compared to many commercial tools) for geospatial analysis, it is one of the best out there.
While it is fairly rich in functionality in terms of doing statistical analysis on ESRI shapefile as well as PostGIS and other formats and also has numerous geometry manipulation features and plugins in its tool belt,
we like the ad-hoc query ability the most. The ease and simplicity of that one tool makes it stand out from the pack. People not comfortable with SQL may not appreciate that feature as much as we do though.
In this excerpt we will quickly go thru the history of project and the ties between the PostGIS group and OpenJump group,
how to install, setup a connection to a PostGIS enabled PostgreSQL database and doing some ad-hoc queries.
GEOS which is a core foundation of PostGIS functionality and numerous other projects, is a C++ port of JTS. New Enhancements often are created in JTS and ported to GEOS and a large body of GEOS work has been incubated
by Refractions Research, the PostGIS incubation company.
One of the nice things about the PostgreSQL command-line restore tool is the ease with which you can restore
select objects from a backup. We tend to use schemas for logical groupings which are partitioned by context, time, geography etc.
Often times when we are testing things, we just want to restore one schema or set of tables from our backup because restoring a 100 gigabyte database
takes a lot of space, takes more time and is unnecessary for our needs. In order to be able to accomplish such a feat, you need to
create tar or compressed (PG custom format) backups. We usually maintain PG custom backups of each of our databases.
As we mentioned in a previous article, RECURSIVE queries, often referred to in SQL ANSI specs and by DB2 and SQL Server as
Common Table Expressions (CTE) will make it into the 8.4 release and can already be found in the dev source. Technically CTE is a
superset and RECURSIVE queries are a subclass of CTE. Looks like basic windowing functionality will make it in 8.4 as well.
One thing I'm really looking forward to have in the upcoming PostgreSQL 8.4 is the introduction
of the WITH RECURSIVE feature that IBM DB2 and SQL Server 2005 already have. Oracle has it too but in a non-standard CONNECT BY so is much less portable.
This is a feature that is perhaps more important to
us for the kind of work we do than the much complained about lack of windowing functions.
I was recently taking a snoop at IBM DB2 newsletter. Why I read magazines and newsletters on databases I don't even use I guess is to see what I'm missing out on
and to sound remotely educated on the topic when I run into one of those people. I also have a general fascination with magazines.
In it their latest newsletter they had examples of doing Fibonacci and Graphs with Common Table Expressions (CTEs).
I'd be interested in seeing what solutions David and others come out with using new features of 8.4. We can see a before 8.4 and after 8.4 recipe.
As a slightly off-topic side note - of all the Database magazines I have read - Oracle Magazine is the absolute worst. SQL Server Magazine and IBM DB2 are pretty decent.
The real problem is that Oracle's magazine is not even a database magazine.
Its a mishmash of every Oracle offering known to man squashed into a compendium that can satisfy no one. You would think that Oracle as big as their database is
would have a magazine dedicated to just that.
Perhaps there is another magazine besides Oracle Magazine, but haven't found it so I would be interested to know if I missed something.