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