From FreeSWITCH Wiki

Contents 1 The Basics 2 Installing 3 Schema Definition 3.1 Defining a Field

The Basics

This module logs call detail records (CDRs) directly to a PostgreSQL database, using the schema defined in the config file (freeswitch/conf/autoload_configs/cdr_pg_csv.conf.xml).

If a record insert fails for whatever reason, the record will be locally spooled to disk, in either CSV or SQL format (user-configurable).

Installing

Debian
To compile/install the module on Debian the PostgreSQL-Server-Devs are needed.

On Squeeze use aptitude to install. You can use

aptitude install postgresql-server-dev-8.4

or

aptitude install libpq-dev libpq5

For compiling the module from Freeswitch uncomment the module in modules.conf and add the module to freeswitch/conf/autoload_configs/modules.conf.xml for autoload.

CentOS 6.x
rpm -ivh http://yum.postgresql.org/9.1/redhat/rhel-6-i386/pgdg-centos91-9.1-4.noarch.rpm
yum -y install postgresql91-devel postgresql91-server postgresql91-odbc postgresql91-plperl

Link your pg_config like this: ln -s /usr/pgsql-9.1/bin/pg_config /usr/bin

For CentOS to use PG 9.X instead of 8.3 (Standard w/git pull version) Series

Modify your Makefile to look like this:

UNAME := $(shell uname -s)
ifeq ($(UNAME),SunOS)
ISA64 := $(shell isainfo -n)
LOCAL_CFLAGS=-I/usr/pgsql-9.1/include
ifneq (,$(findstring m64,$(CFLAGS)))
LOCAL_LDFLAGS=-L/usr/pgsql-9.1/lib/$(ISA64) -R/usr/pgsql-9.1/lib/$(ISA64) -lpq -static
else
LOCAL_LDFLAGS=-L/usr/pgsql-9.1/lib -R/usr/pgsql-9.1/lib -lpq -static
endif
else
LOCAL_CFLAGS=-I/usr/pgsql-9.1/include
LOCAL_LDFLAGS=-L/usr/pgsql-9.1/lib -lpq -static
endif
include ../../../../build/modmake.rules

Schema Definition

The schema section of the config tells the module which channel variable to grab from the call's session, and how to handle them. The SQL INSERT query will be constructed using parameters defined in the schema section, and fields will be in the order they are listed in the config file.

• All fields are treated as strings (eg. they will have single quotes around them in the SQL query), unless specified otherwise.
• If a channel variable resolves to an empty string, it will be cast to a SQL null, unless specified otherwise.
  • The reason for this is to be able to use more efficient, native PostgreSQL column types, such as timestamp (optionally with timezone), inet for storing IPv4/IPv6 addresses, and uuid for natively storing a UUID as a 128 bit binary value. PostgreSQL will not automatically cast empty strings for such fields, and therefore the values need to be handled by the module.

Defining a Field

The absolute minimum required to define a field is the channel variable name. Fields that do not specify a var attribute will be ignored. The following example will attempt to get a value for the channel variable caller_id_number, and will treat it as a string.

 <field var="caller_id_number"/>

Some channel variables should not be treated as strings (eg. quoted), such as numeric values:

 <field var="duration" quote="false"/>

If you don't want the module to cast empty strings to SQL nulls, specify not-null="true". This double-negative might be a bit confusing at first, but you just need to compare it to whether your CDR table field was created with the SQL "NOT NULL" clause. In the following example, empty strings will be left alone and passed to PostgreSQL as-is.

 <field var="caller_id_name" not-null="true"/>

To use a column name that is different to the channel variable name, simply override it in the field definition:

 <field var="local_ip_v4" column="server_address"/>