Thread: Trigger Function Problem

Trigger Function Problem

From
"Van Ingen, Lane"
Date:
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;

Re: Trigger Function Problem

From
Tom Lane
Date:
"Van Ingen, Lane" <lvaningen@ESNCC.com> writes:
>     create_day_flag       varchar default := '';

You can write "default", or you can write ":=" ... but not both.

            regards, tom lane