Josemar Furegatti de Abreu Silva: fevereiro 2015

sexta-feira, 27 de fevereiro de 2015

How to create database link between Oracle XE 11 vs PostgreSQL 9.3

1. Introduction

1.1. Summary

This post shows how to create a database link between Oracle XE vs PostgreSQL

1.2. Scenario

Oracle Linux x86_64 server:

- DNS Name: oracle.mydomain.com
- IP: 192.168.1.20
- Oracle database instance SID=prod

PostgreSQL Linux x86_64 server

- DNS Name: postgresql.mydomain.com
- IP: 192.168.1.20
- PostgreSQL database = prod

1.3. Cookbook

Connect to PostgreSQL and create user for database link and grant appropriated permission
Connect to Oracle, install and configure Unix ODBC packages and drivers
Configure Oracle Heterogeneous Service
Create and test Oracle Database Link to PostgreSQL throught Oracle Heterogeneous Service

2. Step-by-Step

2.1. Connect to PostgreSQL and create user for database link and grant appropriated permission

a) Create PostgreSQL user used by Oracle dblink connection

postgres=# CREATE USER dblink_oracle WITH PASSWORD 'dblink_oracle';
CREATE ROLE

b) Grant connection to dblink user to appropriated PostgreSQL database

postgres=# GRANT CONNECT ON DATABASE prod TO dblink_oracle;
GRANT

c) Grant to dblink user to appropriated schema and database objects permissions

prod=# GRANT USAGE ON SCHEMA schema1 TO dblink_oracle;
GRANT
prod=# GRANT SELECT ON ALL TABLES IN SCHEMA schema1 TO dblink_oracle;
GRANT
prod=#

d) Disconnect and Connect with dblink user to test permissions given

prod=# \q
-bash-4.2$ psql -h localhost -U dblink_oracle -W prod
Password for user dblink_oracle:
psql (9.3.5)
Type "help" for help.

prod=> select table_catalog, table_schema, table_name from information_schema.tables;
table_catalog | table_schema | table_name
---------------+--------------------+---------------------------------------
: : :

2.1. Connect to Oracle Server, install and configure Unix ODBC packages and drivers

a) Install packages required

# yum install unixODBC
# yum install postgresql-odbc

b) Configure connection on ODBC.INI

# vim /etc/odbc.ini
[DB_PGSQL]
Description = DB_PGSQL
Driver = PostgreSQL
Trace = False
TraceFil = /tmp/odbc.log
Database = prod
Servername = postgresql.mydomain.com
Username = dblink_oracle
Password = dblink_oracle
Port = 5432
ReadOnly = Yes

c) Configure driver on ODBCINST.INI

# cat /etc/odbcinst.ini
# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/psqlodbc.so
Setup = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/lib64/psqlodbc.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1

# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc5.so
Setup = /usr/lib/libodbcmyS.so
Driver64 = /usr/lib64/libmyodbc5.so
Setup64 = /usr/lib64/libodbcmyS.so
FileUsage = 1

d) Configure Oracle Heterogeneous Services init file on $ORACLE_HOME/hs/admin/initDB_PGSQL.ora

Change user to oracle installation user on Unix

# su - oracle
$ id
uid=500(oracle) gid=500(dba) groups=500(dba)
$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/xe
$ ls -la $ORACLE_HOME/hs/admin
total 24
drwxr-xr-x 2 oracle oinstall 4096 Dec 20 2013 .
drwxr-xr-x 5 oracle oinstall 4096 Dec 20 2013 ..
-rw-r--r-- 1 oracle oinstall 1109 Sep 17 2011 extproc.ora
-rw-r--r-- 1 oracle oinstall 489 Sep 17 2011 initdg4odbc.ora
-rw-r--r-- 1 oracle oinstall 411 Dec 20 2013 listener.ora.sample
-rw-r--r-- 1 oracle oinstall 244 Dec 20 2013 tnsnames.ora.sample

Let's use Oracle Heterogeneous Service default database gateway file 'initdg4odbc.ora' as base of 'initDB_PGSQL.ora'
Configure key "HS_FDS_SHAREABLE_NAME" to point to appropriated binary installation of your system x86 32bit or 64bit. In my case, it was a 64bits installation '/usr/lib64'
Configure environment variables if necessary. In my case, I needed to force CHARSET

$ cd $ORACLE_HOME/hs/admin
$ cp initdg4odbc.ora initDB_PGSQL.ora
$ vim initDB_PGSQL.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = DB_PGSQL
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so

#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
set CHARSET = UTF8

e) before continuing with Oracle HS configuration, let's check if ODBC connection is working properly. We will use isql to connect throught ODBC connection and try some SQL statement on remote PostgreSQL database

$ -bash-4.1$ odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /u01/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

$ isql DB_PGSQL dblink_oracle dblink_oracle
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select table_catalog, table_schema, count(*) from information_schema.tables group by table_catalog, table_schema;
+---------------+--------------+---------------------+
| table_catalog | table_schema | count |
+---------------+--------------+---------------------+
: : :

f) Configure Oracle HS as database gateway on LISTENER.ORA

Oracle has changed ( since version 10) binary file for HS(Heterogeneous Service) from 'hsodbc' to 'dg4odbc'. Before configure your LISTENER.ORA look for correct binary file for Oracle Database Gateway for Heterogeneous Service ODBC in $ORACLE_HOME/bin/*. In my case, binary file is 'dg4odbc'

$ ls -la $ORACLE_HOME/bin/hsodbc*
ls: cannot access /u01/oracle/app//product/11.2.0/dbhome_1/bin/hsodbc: No such file or directory
$ ls -la $ORACLE_HOME/bin/dg4odbc*
-rwxr-x--x 1 oracle oinstall 536297 Dec 20 2013 /u01/oracle/app//product/11.2.0/dbhome_1/bin/dg4odbc

$ cd $ORACLE_HOME/network/admin/
$ vim listener.ora
# listener.ora Network Configuration File:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = DB_PGSQL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
(PROGRAM = dg4odbc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle.mydomain.com)(PORT = 1521))
)
)

DEFAULT_SERVICE_LISTENER = (XE)

g) Configure TNSNAMES.ORA for HS

$ cd $ORACLE_HOME/network/admin/
$ vim tnsnames.ora
DB_PGSQL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle.mydomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = DB_PGSQL)
)
(HS = OK)
)

h) Restart LISTENER

$ lsnrctl reload

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 27-FEB-2015 12:11:58

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully

i) Create and test Oracle Database Link to PostgreSQL throught Oracle Heterogeneous Service

$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 27 12:16:17 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE PUBLIC DATABASE LINK "DBPG_LNK" CONNECT TO "dblink_oracle" IDENTIFIED BY "dblink_oracle" USING 'DB_PGSQL';

Database link created.

SQL> SELECT DISTINCT "table_catalog", "table_schema" FROM "information_schema"."tables"@DBPG_LNK;

table_catalog table_schema
------------------- -------------------
: :

3. References

terça-feira, 3 de fevereiro de 2015

PostgreSQL for Oracle professional - Source code migration roadmap, command equivalence, comparison, differences, etc

1. Introduction

This post gathers a lot of tips, commands, equivalences, concepts and diferences between PostgreSQL vs Oracle. Itens covered:

Connection; disconnecting; help; listing databases; listing tables; create new databases; connect database; create tablespace; user vs schema; data type; ddl sintax; function sintax.; script file;
characterset; Trigger differences

2. PostgreSQL for Oracle professional

2.1. Connection command line mode, disconnecting and basic help

a) The equivalent to Oracle 'sqlplus' in 'postgre' is an application called 'psql'.

[root@srvpsql ~]# su - postgres

-bash-4.2$ psql

psql (9.3.5)

Type "help" for help.

postgres=#

b) The equivalent Oracle 'sqlplus exit' to exit command line mode use '\q', Unfortunatly 'exit' or 'quit' without '\' does not work ! Using terminator ';' doesn't help.

postgres=# exit

postgres-# exit;

ERROR: syntax error at or near "exit"

LINE 1: exit

postgres=# quit

postgres-# quit;

ERROR: syntax error at or near "quit"

LINE 1: quit

postgres=# \quit

-bash-4.2$

c) To connect using a specific user or to a specific database use command line qualifiers. Equivalent to Oracle 'sqlplus username/password@db_instance'

-bash-4.2$ psql --username=postgres

psql (9.3.5)

Type "help" for help.

postgres=# \quit

d) There are different types of help. You can ask for a basic help, or ask for a SQL help or ask for programming help, etc

postgres=# help

You are using psql, the command-line interface to PostgreSQL.

Type: \copyright for distribution terms

\h for help with SQL commands

\? for help with psql commands

\g or terminate with semicolon to execute query

\q to quit

2.2. Listing existing PostgreSQL databases and Listing existing tables for current database

a) To list existing database use command list database. The equivalent to Oracle 'select * from v$instance'

postgres-# \list

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(3 rows)

b) List database. The equivalent to Oracle 'select * from v$instance'

postgres=# SELECT datname FROM pg_database

postgres-# WHERE datistemplate = false;

datname

----------

postgres

(1 row)

c) List all tables for all database. The equivalent to Oracle 'select * from all_tables'

postgres=# SELECT table_schema,table_name

postgres-# FROM information_schema.tables

postgres-# ORDER BY table_schema,table_name;

table_schema | table_name

--------------------+---------------------------------------

information_schema | administrable_role_authorizations

information_schema | applicable_roles

information_schema | attributes

postgres-# SELECT * FROM pg_catalog.pg_tables ORDER BY table_schema,table_name;

postgres-# \dt * -- all tables for all owners

postgres-# \dt myowner -- all tables for owner myowner

c) Describe table structure

postgres-# \d+ information_schema.tables;

2.3. How to create new PostgreSQL databases

a) PostgreSQL database is quite different from Oracle database_instance. Considering data administration PostgreSQL Database is iquals to Oracle database instance.

postgres=# CREATE DATABASE prod;

CREATE DATABASE

2.4. How to connect specific database

a) The equivalent to Oracle 'sqlplus username/password@db_instance' is:

-bash-4.2$ psql -d prod

psql (9.3.5)

Type "help" for help.

prod=#

b) The equivalent to Oracle 'connect username/password@db_instance' is:

prod=# \connect prod

You are now connected to database "prod" as user "postgres".

2.5. Create tablespace

a) Creating tablespace needs physical directory previous creation

postgres=# select setting||'/base' from pg_settings where name='data_directory';
?column?
------------------------------
/var/lib/pgsql/9.3/data/base
(1 row)postgres=#
postgres=# show data_directory
data_directory
-------------------------
/var/lib/pgsql/9.3/data
(1 row)
postgres=# \q

-bash-4.2$

-bash-4.2$ mkdir /var/lib/pgsql/9.3/data/base/tbs_bi
-bash-4.2$ mkdir /var/lib/pgsql/9.3/data/base/tbs_sig
-bash-4.2$ mkdir /var/lib/pgsql/9.3/data/base/tbs_users
-bash-4.2$
-bash-4.2$ psql prod
psql (9.3.5)
Type "help" for help.
insig_prod=# CREATE TABLESPACE TBS_BI LOCATION '/var/lib/pgsql/9.3/data/base/tbs_bi';
CREATE TABLESPACE
-bash-4.2$

2.6. Create User Schema Owner and User Access

a) Oracle and PostgreSQL have different aproches to access users and owner users:

Oracle: 'user' can be used both to access and own schema.
PostgreSQL: Access user is 'user' and owner user is 'schema'

b) Create PostgreSQL 'schema' and 'user' with the same name

prod=# CREATE USER bi_owner WITH PASSWORD 'bi_owner';

prod=# CREATE SCHEMA bi AUTHORIZATION bi ;

2.7. Oracle vs PostgreSQL DataType equivalence

a) Here is roadmap DataType equivalence between Oracle and PostgreSQL

Oracle PostgreSQL Obs

=============== =============== ==============================================

NUMBER(n,m) NUMERIC(n,m)

VARCHAR2(n) VARCHAR(n)
DATE DATE (*) only DD/MM/YYYY
DATE TIMESTAMP (*) full date/time

2.8. Oracle vs PostgreSQL DDL Sintax and DataType equivalence

a) Create Index can *not* be prefixed by owner sintax

Oracle

CREATE INDEX OWNER.INDEX_NAME ON OWNER.TABLE_NAME( TABLE_COLUMN );

PostgreSQL

SQL> CREATE INDEX INDEX_NAME ON OWNER.TABLE_NAME( TABLE_COLUMN );

2.9. Oracle vs PostgreSQL FUNCTION SINTAX

a) Oracle implicit format does *not* works in PostgreSQL, you need to explicity de format in conversion TO_NUMBER( str [,fmt] )

Oracle:

SQL> SELECT TO_NUMBER(any_column) FROM ANY_TABLE;

PostgreSQL:

prod=# SELECT TO_NUMBER(any_column, '9') FROM ANY_TABLE;

b) Oracle DECODE( ) function does *not* works in PostgreSQL, you need to substitute DECODE( ) for CASE ... WHEN ... THEN ... ELSE ... END

Oracle:

SQL> SELECT DECODE( any_column,

1, 'one',

2, 'two',

'others'

)

FROM ANY_TABLE;

PostgreSQL:

prod=# SELECT CASE WHEN any_column = 1 THEN 'one'

WHEN any_column = 2 THEN 'two'

ELSE 'others'

END

FROM ANY_TABLE;

c) Oracle null value conversion NVL( ) does *not* works in PostgreSQL, you need to use COALESCE( expr#1, expr#2, ..., expr#n )

Oracle:

SQL> SELECT NVL(any_column, 0) FROM ANY_TABLE;

PostgreSQL:

prod=# SELECT COALESCE(any_column, 0) FROM ANY_TABLE;

d) Oracle SYSDATE built-in function does *not* works in PostgreSQL. The ANSI standard defines CURRENT_DATE or CURRENT_TIMESTAMP which is supported by Postgres and documented in the manual:

Oracle:

SQL> SELECT TRUNC(SYSDATE) FROM ANY_TABLE; -- 'dd/mm/yyyy'
SQL> SELECT SYSDATE FROM ANY_TABLE; -- 'dd/mm/yyyy hh24:mm:ssss'

PostgreSQL:

prod=# SELECT CURRENT_DATE FROM ANY_TABLE; -- 'dd/mm/yyyy'

prod=# SELECT CURRENT_TIMESTAMP FROM ANY_TABLE; -- 'dd/mm/yyyy hh24:mm:ssss'

e) Oracle DUAL pseudo table/view does not exists on PostgreSql. Oracle sintax requires ' from ...' to consider a valid statement. PostgreSQL does not need ' from ...', so you just use 'select ...' without from.

Oracle:

SQL> SELECT 'X' FROM DUAL;

PostgreSQL:

prod=# SELECT 'X';

f) Trunc date to first day of month

Oracle:

SQL> SELECT TRUNC(SYSDATE,'MM') FROM DUAL;

PostgreSQL:

prod=# SELECT DATE_TRUNC('month', CURRENT_DATE);

g) Add months to a date

Oracle:

SQL> SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL;

PostgreSQL:

prod=# SELECT DATE_TRUNC('month', CURRENT_DATE) + interval '1 month';

2.10. Executing script file and spool output to a file

a) Spool sqlplus command line output to a file

Oracle: Run sqlplus executing a script file

# sqlplus username/password@db @path_to_script_filename.sql

Oracle: Use spool filename to start output capturing and spool off to stop.

SQL> spool filename
SQL> select * from dual;
DUMMY
-----
X
SQL> spool off

PostgreSQL: Executing a script from command line

-bash-4.2$ psql -U username -d myDataBase -a -f myInsertFile

PostgreSQL: Executing a script from command line

postgres=# \i path_to_sql_file

PostgreSQL: Using capture output to a file on command line

-bash-4.2$ psql -o filename -c 'select * from your_table_name;'

PostgreSQL: Starting capture output to a file on 'psql' command line with \o filename

-bash-4.2$ psql

postgres=# \o spool.txt

postgres=# select * from information_schema.tables;

postgres=# \o

PostgreSQL: Execute all statemments on 'script.sql' and capture output to 'script.log'. Use '-a' or '-echo--all' to echo command

-bash-4.2$ psql -o script.log -f script.sql -a

2.11. SQL*PLUS Prompt, Echo, Host equivalents

a) Oracle's equivalents functionality prompt, echo and host

Oracle:

SQL> prompt 'Hello world'
SQL> host 'ls -la'
SQL> set echo on
SQL> set feedback on

PostgreSQL:

prod=# \echo 'Hello world'
prod=# \echo `ls -la`

2.12. Usefull Script to convert most common Oracle 'sqlplus' sintax into PostgreSQL 'Psql' sintax

This Linux command line converts common Sql*Plus sintax into Psql

postgres=# PG_OWNER_SCHEMA=myschemaowner
for FILE_SQL in *.sql
do
#
echo -n "- $FILE_SQL "
FILE_TMP=$FILE_SQL.tmp
FILE_PSQL=$FILE_SQL.psql
rm -f $FILE_PSQL
cp $FILE_SQL $FILE_PSQL
#
# Step#1: prompt, feedback, echo, define "
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/prompt /\\echo /g' -e 's/set feedback on/\\echo /g' -e 's/set define on/\\echo /g' $FILE_TMP > $FILE_PSQL
#
# Step#2: ("CREATE TABLE ", "ALTER TABLE TABLE_NAME") vs ( <PG_OWNER_SCHEMA>.TABLE_NAME )
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/CREATE TABLE /CREATE TABLE '$PG_OWNER_SCHEMA'./g' -e 's/ALTER TABLE /ALTER TABLE '$PG_OWNER_SCHEMA'./g' $FILE_TMP > $FILE_PSQL
#
# Step#3: ("NUMBER", "VARCHAR2") vs ("NUMERIC", "VARCHAR")
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/ NUMBER/ NUMERIC/g' -e 's/ VARCHAR2/ VARCHAR/g' $FILE_TMP > $FILE_PSQL
#
# Step#4: ("ORACLE_OWNER") vs ("POSTGRESQL_OWNER")
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/ INSIG_OWNER./ insig./g' -e 's/ INBI2./ inbi./g' -e 's/ RM_INTEG./ rm_integ./g' -e 's/ MANAGER./ manager./g' $FILE_TMP > $FILE_PSQL
#
# Step#5: ("TRUNC(SYSDATE)","SYSDATE") vs ("CURRENT_DATE","CURRENT_TIMESTAMP")
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/TRUNC(SYSDATE)/CURRENT_DATE/g' -e 's/SYSDATE/CURRENT_TIMESTAMP/g' $FILE_TMP > $FILE_PSQL
#
# Step#6: ("NVL()") vs ("COALESCE")
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/NVL/COALESCE/g' $FILE_TMP > $FILE_PSQL
#
# Step#7: ("insert into ") vs ("insert into $PG_OWNER_SCHEMA")
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
sed -e 's/insert into /insert into '$PG_OWNER_SCHEMA'./g' $FILE_TMP > $FILE_PSQL
#
# Step#8: Convert CharSet -f ISO-8859-15 -t UTF-8
echo -n "."
rm -f $FILE_TMP
cp $FILE_PSQL $FILE_TMP
iconv -f ISO-8859-15 -t UTF-8 $FILE_TMP > $FILE_PSQL
#
# Step#n: Clean temporary file ...
# Remove '.tmp'
rm -f $FILE_TMP
echo ""
done

postgres=#

2.13. Solving script characterset problem

If you have exported your data into script file (full of insert values), maybe you can face a "character set" problem. Source script data should be generated in a character set compatible to target PostgreSQL database, otherwise you will get a PostgreSQL error. Example of problem:

postgres=# insert into DIM_TAREFA (ID_TAREFA, ID_PROJETO, COD_TAREFA, TAREFA, TAREFA_PAI) values (13834, 5559, '01.13', 'PU75621 - Novo ODS Auditor Pacote 2A ¿Planejament', null);
psql:deploy_09_a_insert_dim.tmp:19797: ERROR: invalid byte sequence for encoding "UTF8": 0xbf

To Convert the source script file to a "character set" compatible to target PostgreSql, using a Linux System, you can follow these steps:

Identify source script file type:

-bash-4.2$ file deploy_09_a_insert_dim.sql
deploy_09_a_insert_dim.sql: ISO-8859 text, with CRLF line terminators

Identify target PostgreSQL database character type:

Convert source script "character set" to target compatible:

-bash-4.2$ iconv -f ISO-8859-15 -t UTF-8 deploy_09_a_insert_dim.sql > deploy_09_a_insert_dim.psql.utf-8

2.14. Trigger differences

Here is the roadmap to convert Oracle trigger to PostgreSQL:

You need to create a PostgreSQL FUNCTION with return type TRIGGER and create a TRIGGER associating event to this function
Oracle reserved keywords ( UPDATING or DELETING or INSERTING ) must be converted to ( TG_OP = 'UPDATE' or TG_OP = 'DELETE' or TG_OP = 'INSERT' );
Oracle reserved keywords to reference line rows before and after changes ( ":NEW." and ":OLD." ) must be converted to ( "NEW." and "OLD" without semicolumn ":" )
PostgreSQL sintax requires language definition after end code. Ex: LANGUAGE plpgsql ;
PostgreSQL sintax requires a start and finish $mark$ for the body of procedure.
References#1: http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html
References#2: http://www.postgresqltutorial.com/creating-first-trigger-postgresql/
References#3: http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html

CREATE OR REPLACE FUNCTION fn_tr_b_er_iud_perfilusuario() RETURNS TRIGGER
AS
$sig_perfil_usuario$
DECLARE
vInfoAudit varchar(2000) := '';
BEGIN
RAISE debug '>>> tr_b_er_iud_perfilusuario >>>';
vInfoAudit := '';
IF ( TG_OP = 'UPDATE' OR TG_OP = 'INSERT') THEN
NEW.dthr_audit := current_timestamp;
END IF;
IF ( TG_OP = 'UPDATE' ) THEN
IF COALESCE(OLD.id_perfil,-1) <> COALESCE(NEW.id_perfil,-1) THEN
vInfoAudit := vInfoAudit || SUBSTR
(
'id_perfil(De: "' || TO_CHAR(OLD.id_perfil,'999999999999') || '", Para: "' || TO_CHAR(NEW.id_perfil,'999999999999') || '"); ',
1, 2000
);
END IF;
ELSIF ( TG_OP = 'DELETE' ) THEN
vInfoAudit := SUBSTR
(
SUBSTR( 'id_perfil(De: "' || TO_CHAR(OLD.id_perfil,'999999999999') || '"); ', 1, 2000) ||
SUBSTR( 'id_menu(De: "' || TO_CHAR(OLD.id_usuario,'999999999999') || '"); ', 1, 2000 ) ||
'', 2000
);
END IF;
--
-- Audit ...
--
IF vInfoAudit IS NOT NULL THEN
INSERT INTO insig.SIG_AUDIT
(
id_audit, dthr_audit, object_audit, event_audit, info_audit, id_object_ref, id_usuario_audit
)
VALUES
(
nextval('insig.SSIG_AUDIT'), -- id_audit
CURRENT_TIMESTAMP, -- dthr_audit
'SIG_PERFIL_USUARIO', -- object_audit
TG_OP, -- event_audit
vInfoAudit, -- info_audit
COALESCE(NEW.id_perfil_usuario,OLD.id_perfil_usuario), -- id_object_ref
COALESCE(NEW.id_usuario_audit,OLD.id_usuario_audit) -- id_usuario_audit
);
END IF;
RAISE debug '<<< tr_b_er_iud_perfilusuario <<<';
RETURN NEW;
END;
$sig_perfil_usuario$
LANGUAGE plpgsql ;
--
DROP TRIGGER tr_b_er_iud_perfilusuario ON insig.sig_perfil_usuario;
--
CREATE TRIGGER tr_b_er_iud_perfilusuario
BEFORE INSERT OR UPDATE OR DELETE
ON insig.sig_perfil_usuario
FOR EACH ROW
EXECUTE PROCEDURE fn_tr_b_er_iud_perfilusuario();

2.16. Debug messages inside trigger and procedure equivalent to Oracle DBMS_OUTPUT.put_line

Oracle

:
dbms_output.put_line( 'I have been here. The id value is ' || :new.id );
:

PostgreSQL

:
RAISE debug 'I have been here. The id value is ' || NEW.id );
:

2.17. Sequence equivalent to Oracle sequence_name.nextval is nextval('sequence_name') on PostgreSql

Oracle:

:
select sequence_name.nextval from dual;
:

PostgreSQL:

:
select nextval('schema.sequence_name')
:

2.18. Handling NO_DATA_FOUND differences between Oracle vs PostgreSql

PostgreSQL SELECT does not raise NO_DATA_FOUND exception unless you add qualifier "STRICT" on "INTO" clause.

Oracle:

DECLARE
vTableName VARCHAR2(30);
BEGIN
DBMS_OUTPUT.PUT_LINE( 'select ...' );
BEGIN
--
SELECT TABLE_NAME
INTO vTableName
FROM USER_TABLES
WHERE 0 =1;
--
DBMS_OUTPUT.PUT_LINE( '... DATA FOUND !' );
--
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE( '... NO DATA FOUND!' );
END;
END;

PostgreSQL:

DO
$$
DECLARE
vTableName VARCHAR(30);
BEGIN
RAISE info 'select ... (#1)' ;
BEGIN
--
SELECT tablename
INTO vTableName
FROM pg_tables
WHERE 0 =1;
--
RAISE info '... DATA FOUND ! (#1)' ;
--
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE info '... NO DATA FOUND!(#1)' ;
END;
--
RAISE info 'select ... (#2)' ;
BEGIN
--
SELECT tablename
INTO STRICT vTableName
FROM pg_tables
WHERE 0 = 1;
--
RAISE info '... DATA FOUND ! (#2)' ;
--
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE info '... NO DATA FOUND!(#2)' ;
END;
END;
$$

;
INFO: select ... (#1)
INFO: ... DATA FOUND ! (#1)
INFO: select ... (#2)
INFO: ... NO DATA FOUND!(#2)
DO