PostgreSQL 8.4 no levenshtein fuzzymatching

For newer version of PG, check out the answer below by Dustin Kirkland

It's an external module for Postgres. You should install the postgresql-contrib-8.4 (or your pg version) package via apt:

apt-get install postgresql-contrib-8.4

Then you find the sql install file somewhere in the /usr/share/postgresql folder, and you'll need to run pgcryto.sql on the database.

psql -d <database> -f /usr/share/postgresql/8.4/contrib/pgcrypto.sql

Or,

$ cd /usr/share/postgresql/8.4/contrib
$ psql -d <database>
    psql (8.4.8)
    Type "help" for help.

    database=# \i pgcrypto.sql

Your function had a number of problems. Try this revised version:

CREATE OR REPLACE FUNCTION add_column(
  full_name   regclass
 ,column_name text
 ,data_type   regtype)
  RETURNS BOOLEAN AS
$func$
BEGIN

IF EXISTS (
      SELECT 1
      FROM   pg_attribute
      WHERE  attrelid = full_name
      AND    attname = column_name
      AND    attisdropped = FALSE) THEN

   RAISE NOTICE 'Column "%" % already exists in %.'
                ,column_name, data_type, full_name;
   RETURN FALSE;
END IF;

-- For Postgres 9.1+:
EXECUTE format('ALTER TABLE %s ADD COLUMN %I %s;'
                ,full_name, column_name, data_type); 

-- For Postgres 8.4-:
-- EXECUTE 'ALTER TABLE '|| full_name
--      || ' ADD COLUMN ' || quote_ident(column_name) || ' ' || data_type;

RAISE NOTICE 'Column "%" % added to %.'
             ,column_name, data_type, full_name;
RETURN TRUE;

END
$func$ LANGUAGE plpgsql;

Tested with PostgreSQL 9.1.7.

• You do not need dynamic SQL at all to check the catalog table(s) for existence of the column. Simplify that to a plain EXISTS expression.

• It's regularly faster by two orders of magnitude to use the table pg_catalog.pg_attribute instead of the bloated view information_schema.columns.

  information_schema is only good for SQL standard compliance and portability of code. Since you are writing 100 % Postgres-specific code in a plpgsql function, neither is relevant here. And the basic layout of pg_attribute is not going to change across major Postgres versions either.

• format() was introduced with Postgres 9.1. Use this convenient function in modern Postgres. For version 8.4 and older use the commented alternative in the code.

• Do not quote the language name plpgsql at the end! It's an identifier, not a value. Single quotes Will make the command fail when using upper case letters. Generally, it is essential to know the difference between identifiers and values.

• I replaced the two parameters schema_name text, table_name text with fullname regclass. This has a number of advantages and no disadvantage AFAICS.

  1. Shorter code and simpler call.
  2. It's simple to supply the schema name where needed and omit it where not.
  3. Cast to regclass checks for existence of the table immediately and raises an exception if not. Also prevents SQLi effectively.
  4. If you don't provide a schema-qualified name, the search_path is used automatically. If the table name is unambiguous given the current search_path the schema name is not printed either when converted to text.
  5. When (automatically) converted to text, schema and / or table name are automatically double-quoted where needed. Voids the need for quote_ident(), escaping as identifier in format() or adding quotes in messages.

• Similarly I use regtype for the data_type. Similar advantages. Read more about object identifier types in the manual.

• Simpler RAISE syntax.

• The concatenation operator in Postgres (and standard SQL) is ||. + is an extension of the standard in SQL Server that does not work with most other RDBMS.