You have a very aggravated person who demands you purge their email from any table you have in your system.
You have lots of tables that have email addresses. How do you find which tables have this person's email address.
Below is a handy plpgsql function we wrote that does the following. Given a search criteria, field name pattern, table_name pattern,
schema name pattern, data type pattern, and max length of field to check, it will search all fields in the database fitting those
patterns and return to you the names of these schema.table.field names that contain the search phrase.
To use the below you would do something like:
SELECT pc_search_tablefield('%john@hotmail%', '%email%', '%', '%', '%', null);
The above will return all database field names that have the phrase email in the field name and that contain the term john@hotmail
Microsoft Access has these peculiar set of aggregates called First and Last. We try to avoid them because while the concept is useful, we find Microsoft Access's implementation of them
a bit broken. MS Access power users we know moving over to something like MySQL, SQL Server, and PostgreSQL often ask - where's first and where's last?
First we shall go over what exactly these aggregates do in MS Access and how they are different from MIN and MAX and what they should do in an ideal world. Then we shall create our ideal
world in PostgreSQL.
One of the things we love most about PostgreSQL is the ease with which one can define new aggregate functions with even a language as
succinct as SQL. Normally when we have needed a median function, we've just used the built-in median function in PL/R as we briefly demonstrated in
Language Architecture in PostgreSQL.
If all you demand is a simple median aggregate function ever then installing the whole R statistical environment so you can use PL/R is overkill and much less
In this article we will demonstrate how to create a Median function with nothing but the built-in PostgreSQL SQL language, array constructs,
Every once in a while - particularly if you are using inherited tables, you forget to put an important index on one of your tables
which bogs down critical queries. Its sometimes convenient to inspect the index catalog to see what tables are missing indexes or
what tables are missing a critical index. Normally we try to stick with querying the information_schema because queries against that
schema work pretty much the same in PostgreSQL as they do in SQL Server and MySQL. For most of the examples below we had to delve into pg_catalog schema territory
since there was no view we could find in information_schema that would give us enough detail about indexes.