Re: Ok, how do I debug THIS?

--On Tuesday, May 13, 2003 12:45:26 -0700 Josh Berkus <josh(at)agliodbs(dot)com>
wrote:

> Larry,
>
>> > You seem to be missing most of your function, here.
>> I just did a head. Note the line count above. It's only 147
>> lines, but the error refers to line 206.
>
> Obviously postgres is counting lines differently from you.
>
> Post the whole thing, or start from the bottom of the function and work
> your way up until you find the problem. Most likely issue: missing
> semicolon.

Ok, I'm blind or stupid, or quoting rules are biting my butt....

CREATE OR REPLACE FUNCTION networks_trigger_log () RETURNS "trigger"
AS 'DECLARE
record_fields_old text;
record_values_old text;
record_fields_new text;
record_values_new text;
query_string_old text;
query_string_new text;
BEGIN
query_string_old := ''INSERT INTO networks_log'';
query_string_new := ''INSERT INTO networks_log'';
record_fields_old := ''user_id,update_at,update_type,update_ver'';
record_fields_new := ''user_id,update_at,update_type,update_ver'';
record_values_old := ''CURRENT_USER,now,'' || quote_literal(TG_OP) ||
'','' || quote_literal(''O'');
record_values_new := ''CURRENT_USER,now,'' || quote_literal(TG_OP) ||
'','' || quote_literal(''N'');
IF TG_OP = ''INSERT''
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''netblock'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.netblock) || '';
IF NEW.router NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''router'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.router) || '';
END IF;
IF NEW.interface NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''interface'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.interface) || '';
END IF;
IF NEW.dest_ip NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''dest_ip'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.dest_ip) || '';
END IF;
IF NEW.mis_token NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''mis_token'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.mis_token) || '';
END IF;
IF NEW.assigned_date NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''assigned_date'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.assigned_date) || '';
END IF;
IF NEW.assigned_by NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''assigned_by'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.assigned_by) || '';
END IF;
IF NEW.justification_now NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''justification_now'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.justification_now) || '';
END IF;
IF NEW.justification_1yr NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''justification_1yr'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.justification_1yr) || '';
END IF;
IF NEW.cust_asn NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''cust_asn'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.cust_asn) || '';
END IF;
IF NEW.cust_asn NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''cust_asn'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.cust_asn) || '';
END IF;
IF NEW.comments NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''comments'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.comments) || '';
END IF;
IF NEW.other_reference NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''other_reference'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.other_reference) || '';
END IF;
IF NEW.parent_asn NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''parent_asn'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.parent_asn) || '';
END IF;
IF NEW.status NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''status'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.status) || '';
END IF;
IF NEW.purpose NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''purpose'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.purpose) || '';
END IF;
IF NEW.customer_reference NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''customer_reference'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.customer_reference) || '';
END IF;
IF NEW.natblock NOTNULL
THEN
record_fields_new := record_fields_new || '','' ||
quote_ident(''natblock'');
record_values_new := record_values_new || '','' ||
|| quote_literal(NEW.natblock) || '';
END IF;
query_string_new := query_string_new || ''('' ||
record_fields_new || '') VALUES('' ||
record_values_new || '')'';
EXECUTE query_string_new;
END IF;
END;'
LANGUAGE plpgsql;

>
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749