Trigger Function Problem - Mailing list pgsql-novice
From | Van Ingen, Lane |
---|---|
Subject | Trigger Function Problem |
Date | |
Msg-id | A3FF4275060B76459B5C08A64AE330C805C3A1@twmail.esncc.com Whole thread Raw |
Responses |
Re: Trigger Function Problem
|
List | pgsql-novice |
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;
pgsql-novice by date: