I am using PostgreSQL 8.0.1 in Windows 2003.
I am getting the following error on a trigger function; can't figure out what is wrong:
ERROR: syntax error at or near ":" at character 9
QUERY: SELECT := ''
CONTEXT: PL/pgSQL function "update_rpt_history" line 56 at block variables initialization
Google shows only a few of these errors, but still can't figure out what is the cause or what to do to
fix / avoid this.
What I did so far / questions:
- looked at all assignment operators ( := ), they look fine to me
- looked for a SELECT := '', but found none; and, line 56 has no 'SELECT' statement
- question: not sure where 'block variables initialization' is; error message does not show up in error
codes of PostgreSQL doc manual
- question: where do the 'RAISE' commands display? Was using them to diagnose what line the
function failed on.
- not sure how PostgreSQL counts lines; assume it will count all lines except comment lines (--);
if true, the 56th line would be an attempt to store a trigger variable:
process_name := TG_ARGV[0];
Here is what the first 100 lines or so look like:
CREATE OR REPLACE FUNCTION update_rpt_history()
RETURNS "trigger" AS
$BODY$
DECLARE
-- types of history tables used
crc varchar := 'crc';
neighbor varchar := 'neighbor';
if_rf varchar := 'if_rf';
usage varchar := 'usage';
-- other variables
adns_if_rf_hist record;
create_day_flag varchar default := '';
create_hour_flag varchar default := '';
history_record record;
ifrf_recs integer;
interval_seconds bigint;
loop_count_day smallint;
loop_count_day_limit smallint default := 7;
loop_count_hour smallint;
loop_count_hour_limit smallint default := 24;
multiple_neighbors smallint;
neighbor_state smallint;
neighbor_state_0 integer;
neighbor_state_1 integer;
no varchar default := 'N';
process_name varchar;
remote_net varchar;
rf_type varchar;
rpt_day_hist_prev adns_report_day_history%ROWTYPE;
rpt_day_hist_curr adns_report_day_history%ROWTYPE;
rpt_hour_hist_prev adns_report_day_history%ROWTYPE;
rpt_hour_hist_curr adns_report_day_history%ROWTYPE;
usage_activity adns_if_usage%ROWTYPE;
work_bytes_possible double precision;
work_bytes_remote double precision;
work_day smallint;
work_ifID bigint;
work_ifRfType varchar;
work_ifSpeed double precision;
work_hour smallint;
work_day_key_curr timestamp;
work_day_key_prev timestamp;
work_day_traffic_total integer;
work_hour_key_curr timestamp;
work_hour_key_prev timestamp;
work_hour_traffic_total integer;
work_ifrf_begin_day timestamp;
work_ifrf_begin_hour timestamp;
work_last_updatedTime timestamp;
work_records integer;
work_records_read integer;
work_remoteNet varchar;
work_reportName varchar;
work_seconds integer;
work_total double precision;
yes varchar default := 'Y';
BEGIN
RAISE NOTICE 'Message 1';
-- obtain source of activity info
process_name := TG_ARGV[0];
RAISE NOTICE 'Message 2';
-- determine the history record to post activity against
select into work_hour_key_curr date_trunc('hour', TIMESTAMP
NEW.updatedTime);
select into work_day_key_curr date_trunc('day', TIMESTAMP
NEW.updatedTime);
RAISE NOTICE 'Message 3';
-- determine values of main report history flds
work_ifID := NEW.ifID;
if process_name = ifrf then
work_reportName := NEW.ifID;
work_remoteNet := NEW.remoteNet;
select into work_ifRfType 'R' ||
ltrim(to_char(ifRfType,'000000000009'),'0')
from adns_if_rf_history
where ifID = NEW.ifID
and updatedTime <= NEW.updatedTime
order by updatedTime desc limit 1;
elseif
(process_name = crc) or (process_name = usage) then
work_report_name := NEW.ifID;
select into work_remoteNet remoteNet from adns_if_remote_net_history
where ifID = NEW.ifID
and updatedTime <= NEW.updatedTime
order by updatedTime desc limit 1;
select into work_ifRfType 'R' ||
ltrim(to_char(ifRfType,'000000000009'),'0')
from adns_if_rf_history
where ifID = NEW.ifID
and updatedTime <= NEW.updatedTime
order by updatedTime desc limit 1;
else -- process_name = neighbor
work_reportName = NEW.remoteNet;
work_remoteNet := NEW.remoteNet;
select into work_ifRfType 'R' ||
ltrim(to_char(ifRfType,'000000000009'),'0')
from adns_if_rf_history
where ifID = NEW.ifID
and updatedTime <= NEW.updatedTime
order by updatedTime desc limit 1;
end if;
