Help !! Cisco h323 Billing

[Peter Nixon]

On Sat 23 Sep 2006 22:26, you wrote:
> Dear Peter Nixon,
>                              Following the instructions you provided in
> Freeradius/Billing, I encounter the following error message, I've try to
> create the function, but the function can't
>                          be created, could you help me ?  Thx !!
>
> Best Regards
> Roy.
>
> ---------------------------------------------------------------------------
>-------------------------------------------------------------------- lm_sql
> (pgsql-voip): sql_set_user escaped user --> '1009'
> radius_xlat:  'INSERT into StopVOIP ??(RadiusServerName, UserName,
> NASIPAddress, AcctTime, ??AcctSessionTime, AcctInputOctets,
> AcctOutputOctets, CalledStationId, CallingStationId, ??AcctDelayTime,
> H323RemoteAddress, H323VoiceQuality, CiscoNASPort, h323callorigin, callid,
> ??h323connecttime, h323disconnectcause, h323disconnecttime, h323gwid,
> h323setuptime) ??values('myservername', '1009', '211.76.228.84', now(),
> '1', ??'0', '0', '9001', '1009', ??'0', NULLIF('211.76.228.31', '')::inet,
> NULLIF('0','')::integer, NULLIF('', ''), ??'originate',
> pick_id('20060924031453-0045-0000F6-1', ''), strip_dot('03:14:53.000 UTC
> Sun Sep 24 2006'), '200', ??strip_dot('03:14:54.000 UTC Sun Sep 24 2006'),
> ' 59.126.157.203', strip_dot('03:14:53.000 UTC Sun Sep 24 2006'))'
> rlm_sql (pgsql-voip): Reserving sql socket id: 24
> rlm_sql_postgresql: Status: PGRES_FATAL_ERROR
> rlm_sql_postgresql: affected rows =
> rlm_sql_postgresql: Postgresql check_error: PGRES_FATAL_ERROR, returning
> SQL_DOWN
> rlm_sql (pgsql-voip): Attempting to connect rlm_sql_postgresql #24
> rlm_sql (pgsql-voip): Connected new DB handle, #24
> rlm_sql_postgresql: Status: PGRES_FATAL_ERROR
> rlm_sql_postgresql: affected rows =
> rlm_sql_postgresql: Postgresql check_error: PGRES_FATAL_ERROR, returning
> SQL_DOWN
> rlm_sql (pgsql-voip): failed after re-connect
> rlm_sql (pgsql-voip): Couldn't update SQL accounting STOP record - ERROR:
> function pick_id("unknown", "unknown") does not exist HINT:  No function
> matches the given name and argument types. You may need to add explicit
> type casts.
> rlm_sql (pgsql-voip): Released sql socket id: 24
>   modcall[accounting]: module "pgsql-voip" returns fail for request 0
> modcall: leaving group accounting (returns fail) for request 0
> Finished request 0
> ---------------------------------------------------------------------------
>------------------------------------------------------------------------
> -bash-3.1$ psql radius < /cisco_h323_db_sample_functions-postgres.sql
> ERROR:  syntax error at or near "/" at character 547
> LINE 14:  *  $datetime =~ s/^\\.*//;
>                                   ^
> ERROR:  syntax error at or near "*" at character 1
> LINE 1: *         return $datetime;
>         ^
> ERROR:  syntax error at or near "*" at character 1
> LINE 1: * ' language 'plperl';
>         ^
> ERROR:  syntax error at or near "new_number" at character 1
> LINE 1: new_number VARCHAR;
>         ^
> ERROR:  syntax error at or near "new_number" at character 15
> LINE 2:         new_number := split_part(original_number,''#'',2);
>                 ^
> ERROR:  syntax error at or near "IF" at character 1
> LINE 1: IF new_number = '''' THEN
>         ^
> ERROR:  syntax error at or near "ELSE" at character 1
> LINE 1: ELSE RETURN new_number;
>         ^
> ERROR:  syntax error at or near "IF" at character 5
> LINE 1: END IF;
>             ^
> WARNING:  there is no transaction in progress
> COMMIT
> ERROR:  syntax error at or near "' LANGUAGE '" at character 1
> LINE 1: ' LANGUAGE 'plpgsql';
>         ^
> ERROR:  syntax error at or near "new_number" at character 1
> LINE 1: new_number VARCHAR;
>         ^
> ERROR:  syntax error at or near "clean_number" at character 1
> LINE 1: clean_number VARCHAR;
>         ^
> ERROR:  syntax error at or near "new_number" at character 15
> LINE 2:         new_number := split_part(original_number,''#'',2);
>                 ^
> ERROR:  syntax error at or near "IF" at character 1
> LINE 1: IF new_number = '''' THEN
>         ^
> ERROR:  syntax error at or near "ELSE" at character 1
> LINE 1: ELSE clean_number := new_number;
>         ^
> ERROR:  syntax error at or near "IF" at character 5
> LINE 1: END IF;
>             ^
> ERROR:  syntax error at or near "IF" at character 1
> LINE 1: IF substring(clean_number from 1 for 2) = ''00'' THEN
>         ^
> ERROR:  syntax error at or near "ELSIF" at character 1
> LINE 1: ELSIF substring(clean_number from 1 for 1) = ''0'' THEN
>         ^
> ERROR:  syntax error at or near "ELSE" at character 1
> LINE 1: ELSE
>         ^
> ERROR:  syntax error at or near "IF" at character 5
> LINE 1: END IF;
>             ^
> WARNING:  there is no transaction in progress
> COMMIT
> ERROR:  syntax error at or near "' LANGUAGE '" at character 1
> LINE 1: ' LANGUAGE 'plpgsql';
>         ^
> ERROR:  syntax error at or near "new_number" at character 1
> LINE 1: new_number VARCHAR;
>         ^
> ERROR:  syntax error at or near "clean_number" at character 1
> LINE 1: clean_number VARCHAR;
>         ^
> ERROR:  syntax error at or near "new_number" at character 15
> LINE 2:         new_number := split_part(original_number,''#'',2);
>                 ^
> ERROR:  syntax error at or near "IF" at character 1
> LINE 1: IF new_number = '''' THEN
>         ^
> ERROR:  syntax error at or near "ELSE" at character 1
> LINE 1: ELSE clean_number := new_number;
>         ^
> ERROR:  syntax error at or near "IF" at character 5
> LINE 1: END IF;
>             ^
> ERROR:  syntax error at or near "IF" at character 1
> LINE 1: IF substring(clean_number from 1 for 2) = ''00'' THEN
>         ^
> ERROR:  syntax error at or near "ELSIF" at character 1
> LINE 1: ELSIF substring(clean_number from 1 for 1) = ''0'' THEN
>         ^
> ERROR:  syntax error at or near "ELSE" at character 1
> LINE 1: ELSE
>         ^
> ERROR:  syntax error at or near "IF" at character 5
> LINE 1: END IF;
>             ^
> WARNING:  there is no transaction in progress
> COMMIT
> ERROR:  syntax error at or near "' LANGUAGE '" at character 1
> LINE 1: ' LANGUAGE 'plpgsql';
>         ^
> ERROR:  syntax error at or near "new_number" at character 1
> LINE 1: new_number VARCHAR;
>         ^
> ERROR:  syntax error at or near "clean_number" at character 1
> LINE 1: clean_number VARCHAR;
>         ^
> ERROR:  syntax error at or near "new_number" at character 15
> LINE 2:         new_number := split_part(original_number,''#'',2);
>                 ^
> ERROR:  syntax error at or near "IF" at character 1
> LINE 1: IF new_number = '''' THEN
>         ^
> ERROR:  syntax error at or near "ELSE" at character 1
> LINE 1: ELSE clean_number := new_number;
>         ^
> ERROR:  syntax error at or near "IF" at character 5
> LINE 1: END IF;
>             ^
> ERROR:  syntax error at or near "IF" at character 1
> LINE 1: IF substring(clean_number from 1 for 2) = ''00'' THEN
>         ^
> ERROR:  syntax error at or near "ELSIF" at character 1
> LINE 1: ELSIF substring(clean_number from 1 for 1) = ''0'' THEN
>         ^
> ERROR:  syntax error at or near "ELSE" at character 1
> LINE 1: ELSE
>         ^
> ERROR:  syntax error at or near "IF" at character 5
> LINE 1: END IF
>             ^
> ERROR:  syntax error at or near "' LANGUAGE '" at character 1
> LINE 1: ' LANGUAGE 'plpgsql';
>         ^
> ERROR:  syntax error at or near "key2" at character 1
> LINE 1: key2 ALIAS FOR $2;
>         ^
> ERROR:  syntax error at or near "key3" at character 1
> LINE 1: key3 ALIAS FOR $16;
>         ^
> ERROR:  syntax error at or near "PERFORM" at character 15
> LINE 2:         PERFORM radacctid FROM StopVoIP WHERE h323SetupTime ...
>                 ^
> ERROR:  syntax error at or near "IF" at character 1
> LINE 1: IF NOT FOUND THEN
>         ^
> ERROR:  syntax error at or near "RETURN" at character 1
> LINE 1: RETURN true;
>         ^
> ERROR:  syntax error at or near "IF" at character 5
> LINE 1: END IF;
>             ^
> ERROR:  syntax error at or near "RETURN" at character 1
> LINE 1: RETURN false;
>         ^
> WARNING:  there is no transaction in progress
> COMMIT
> ERROR:  syntax error at or near "' LANGUAGE '" at character 1
> LINE 1: ' LANGUAGE 'plpgsql';
>         ^
> ERROR:  syntax error at or near "IF" at character 1
> LINE 1: IF NOT FOUND THEN
>         ^
> ERROR:  syntax error at or near "RETURN" at character 1
> LINE 1: RETURN true;
>         ^
> ERROR:  syntax error at or near "IF" at character 5
> LINE 1: END IF;
>             ^
> ERROR:  syntax error at or near "RETURN" at character 1
> LINE 1: RETURN false;
>         ^
> WARNING:  there is no transaction in progress
> COMMIT
> ERROR:  syntax error at or near "' LANGUAGE '" at character 1
> LINE 1: ' LANGUAGE 'plpgsql';

Hi Roy

It appears that your postgresql installation does not have 'plpgsql' 
or 'plperl' language support installed. Install that and it should work.

-- 

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20060924/4091623d/attachment.pgp>