Re: Ok, how do I debug THIS? - Mailing list pgsql-sql
| From | Larry Rosenman |
|---|---|
| Subject | Re: Ok, how do I debug THIS? |
| Date | |
| Msg-id | 273950000.1052855357@lerlaptop.iadfw.net Whole thread Raw |
| In response to | Re: Ok, how do I debug THIS? (Josh Berkus <josh@agliodbs.com>) |
| Responses |
Re: Ok, how do I debug THIS?
(Stephan Szabo <sszabo@megazone23.bigpanda.com>)
|
| List | pgsql-sql |
--On Tuesday, May 13, 2003 12:45:26 -0700 Josh Berkus <josh@agliodbs.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_oldtext; 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) || '';
ENDIF; 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_referenceNOTNULL 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_referenceNOTNULL 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