Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Strings are concatenated using ||, not + –  a_horse_with_no_name Feb 28 '13 at 19:21

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
wow, thanks for the lesson, I really appreciate it. One thing, i was using text for instead of regtype because when I passed 'vchar(200)' it would turn this into a vhcar without a limit. Is there a way to make this work correctly? The linked document didn't really touch on this. –  GuidoS Mar 5 '13 at 13:42
    
@GuidoS: Sorry, can you rephrase that question? I don't get it. –  Erwin Brandstetter Mar 5 '13 at 14:04

This works (not extensively tested):

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 'ALTER TABLE ' || schema_name || '.' || table_name 
         || ' ADD COLUMN ' || column_name || ' ' || data_type || ';';

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

   RETURN TRUE; 
END; 
$BODY$ LANGUAGE plpgsql;

I simply removed the + signs and replaced with ||. On the EXECUTE I removed the format and simply concatenated the strings.

share|improve this answer
    
information_schema.columns can looked up with plain SQL. No need for EXECUTE there. –  Erwin Brandstetter Mar 1 '13 at 1:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.