String handling in postgres 8.4

This started as a question about indexes, but then when I got my answer I realized I would have to try and convert the answer into 8.4 syntax (I can't believe that format does not exist).

I thought I was handling the variables correctly, but for some reason I can't get the typing to function properly:

CREATE OR REPLACE FUNCTION add_column(schema_name TEXT, table_name
TEXT,  column_name TEXT, data_type TEXT) RETURNS BOOLEAN AS $BODY$
DECLARE   _tmp text;
BEGIN

   EXECUTE 'SELECT COLUMN_NAME FROM information_schema.columns WHERE 
     table_schema='''+|| schema_name+'''
     AND table_name='''+||table_name+'''
     AND column_name='''+||column_name+''''   INTO _tmp;

   IF _tmp IS NOT NULL THEN
     RAISE NOTICE USING MESSAGE = 'Column '+||column_name+' already exists in '+||schema_name+'.'+||table_name;
     RETURN FALSE;   END IF;

   EXECUTE format('ALTER TABLE %I.%I ADD COLUMN %I %s;', schema_name,
 table_name, column_name, data_type);

     RAISE NOTICE USING MESSAGE = 'Column '+||column_name+' added to '+||schema_name+'.'+||table_name;

   RETURN TRUE;
END; $BODY$ LANGUAGE 'plpgsql';

Here is the error message I get:


ERROR: operator does not exist: text + unknown

Does anyone know how I can get this to work in 8.4?

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.

• Shorter code and simpler call.

• It's simple to supply the schema name where needed and omit it where not.

• Cast to regclass checks for existence of the table immediately and raises an exception if not. Also prevents SQLi effectively.

• 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.

• 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.

StackExchange Database Administrators Q#35683, answer score: 3