Alter column bytea to text in various versions of Postgres with plpgsql

Question

I have problem how to convert column using plpgsql (bytea -> text). I wrote function which works on some databases and doesn't for others. I don't know how to fix it.
Using databases from 8.0 - 9.3; this error is for 8.1.19.

I received:

ERROR:  column "the_column" cannot be cast to type "text"
CONTEXT:  SQL statement "ALTER TABLE the_table ALTER COLUMN the_column TYPE text"
PL/pgSQL function "byteatotext" line 11 at execute statement

My function:

CREATE OR REPLACE FUNCTION byteaToText()
  RETURNS text AS
  $BODY$
    DECLARE
      ver int;
    BEGIN
      SELECT into ver (select setting from pg_settings where name='server_version_num') as test;
      IF ver < 80200 THEN
        EXECUTE 'ALTER TABLE the_table ALTER COLUMN the_column TYPE text USING ENCODE(properties, \'escape\'))';
        RETURN ver;
      ELSE
        EXECUTE 'ALTER TABLE the_table ALTER COLUMN the_column TYPE text';
        RETURN ver;
      END IF;
    RETURN 'error';
    END;
  $BODY$
  LANGUAGE 'plpgsql' VOLATILE;

select byteaToText();

score 1 · Accepted Answer · answered Feb 24 '15 at 02:26

The configuration parameter server_version_num was introduced with Postgres 8.2. Per documentation:

Add new configuration parameter server_version_num (Greg Sabino Mullane)

This is like server_version, but is an integer, e.g. 80200. This allows applications to make version checks more easily.

Your code can't find the nonexistent parameter in pg 8.1 and doesn't assign to ver, so it stays NULL and control ends up in the ELSE branch - which is also indicated in the error message:

ERROR:  column "the_column" cannot be cast to type "text"
CONTEXT:  SQL statement "ALTER TABLE the_table ALTER COLUMN the_column TYPE text"
PL/pgSQL function "byteatotext" line 11 at execute statement

Rewrite the function to:

CREATE OR REPLACE FUNCTION bytea_to_text()
  RETURNS text AS
$func$
BEGIN
   -- the config param was introduced with version 8.2
   PERFORM 1 FROM pg_settings WHERE name = 'server_version_num';

   IF FOUND THEN  -- version >= 8.2
      EXECUTE 'ALTER TABLE the_table ALTER COLUMN the_column TYPE text';
   ELSE
      EXECUTE 'ALTER TABLE the_table ALTER COLUMN the_column TYPE text USING encode(the_column, ''escape''))';
   END IF;

   RETURN (SELECT setting FROM pg_settings WHERE name = 'server_version');
END
$func$ LANGUAGE plpgsql;

Notes

Untested on Postgres 8.1, nobody uses Postgres that old any more.
Return the config param server_version instead, which also exists in pg 8.1.
I replaced properties with the_column, assuming this is another error.
Don't use mixed case names.
Don't quote the language name. Use LANGUAGE plpgsql, no quotes.

Aside:
The obvious solution would be upgrade your Postgres servers to a version that's running on electricity, not steam. Postgres 8.1 was written 10 years ago and has reached EOL in November 2010. Barring that, at least update to the last point release, which is 8.1.23.

Alter column bytea to text in various versions of Postgres with plpgsql

1 Answers1

Notes