replace_empty_string

Last half of year I am working on migration relative big application from Oracle to Postgres. This application is based on intensive usage of stored procedures, triggers, views. The base tools are ora2pg and plpgsql_check. Many thanks to Gilles Darold for his work on ora2pg. Half year ago this tool has almost zero support for PL/SQL - and now it is able to translate 90% of big code base of old PL/SQL code to PLpgSQL. There was lot of issues, but often was fixed to next day. Thank you.

Some tools I had to write too. I have some points for Orafce. Last tool what I wrote for this project is replace_empty_string extension. Oracle doesn't save empty strings - it does translation to NULL implicitly. To ensure similar behave I wrote generic trigger, that any empty string replaces by NULL. Default is quite behave, but warning (when string is empty string) is possible.

Example:

CREATE EXTENSION replace_empty_string;

CREATE TABLE res (
 id  int4,
 idesc  text,
 test1  varchar,
 test2  text
);

CREATE TRIGGER res_replace_empty_string
 BEFORE UPDATE OR INSERT ON res
 FOR EACH ROW
 EXECUTE PROCEDURE replace_empty_string ();

INSERT INTO res VALUES (1, 'first', NULL, '');
INSERT INTO res VALUES (2, NULL, '', 'Hello');

\pset null ****

SELECT * FROM res;
 id | idesc | test1 | test2 
----+-------+-------+-------
  1 | first | ****  | ****
  2 | ****  | ****  | Hello
(2 rows)

UPDATE res SET idesc = ''
 WHERE id = 1;

SELECT * FROM res;
 id | idesc | test1 | test2 
----+-------+-------+-------
  2 | ****  | ****  | Hello
  1 | ****  | ****  | ****
(2 rows)