X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0E65547636B for ; Tue, 13 May 2003 15:49:23 -0400 (EDT) Received: from lerami.lerctr.org (lerami.lerctr.org [207.158.72.11]) by postgresql.org (Postfix) with ESMTP id 1D608476348 for ; Tue, 13 May 2003 15:49:21 -0400 (EDT) Received: from lerlaptop.iadfw.net (lerlaptop.iadfw.net [206.66.13.21]) (authenticated bits=0) by lerami.lerctr.org (8.12.9/8.12.9/20030225) with ESMTP id h4DJnIKK019872; Tue, 13 May 2003 14:49:18 -0500 (CDT) Date: Tue, 13 May 2003 14:49:17 -0500 From: Larry Rosenman To: josh@agliodbs.com, pgsql-sql@postgresql.org Subject: Re: Ok, how do I debug THIS? Message-ID: <273950000.1052855357@lerlaptop.iadfw.net> In-Reply-To: <200305131245.26203.josh@agliodbs.com> References: <246160000.1052853868@lerlaptop.iadfw.net> <200305131231.34175.josh@agliodbs.com> <256400000.1052854454@lerlaptop.iadfw.net> <200305131245.26203.josh@agliodbs.com> X-Mailer: Mulberry/3.0.3 (Linux/x86) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: by amavisd-milter (http://amavis.org/) X-Spam-Status: No, hits=-26.6 required=5.0 tests=BAYES_01,IN_REP_TO,QUOTED_EMAIL_TEXT,QUOTE_TWICE_1, REFERENCES,REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/242 X-Sequence-Number: 13407 --On Tuesday, May 13, 2003 12:45:26 -0700 Josh Berkus 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@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749