Thread: Ok, how do I debug THIS?
I'm getting:
ERROR: parse error at or near "IF"
WARNING: plpgsql: ERROR during compile of networks_trigger_log near line
206
$
but the function is:
$ wc -l z 147 z
$ head z
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'';
$
How do I find my stupidity?
$ psql
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands
\g or terminate with semicolon to execute query \q to quit
ler=# select version(); version
---------------------------------------------------------------------PostgreSQL 7.3.2 on i386-portbld-freebsd4.7,
compiledby GCC 2.95.4
(1 row)
ler=#
--
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
Larry, > but the function is: > > $ wc -l z > 147 z > $ head z > 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''; You seem to be missing most of your function, here. -- -Josh BerkusAglio Database SolutionsSan Francisco
--On Tuesday, May 13, 2003 12:31:34 -0700 Josh Berkus <josh@agliodbs.com> wrote: > Larry, > >> but the function is: >> >> $ wc -l z >> 147 z >> $ head z >> 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''; > > 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. How do I debug it? (I can post the whole thing, but it's butt ugly (still in development)). > > -- > -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
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. -- -Josh BerkusAglio Database SolutionsSan Francisco
--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
On Tue, 13 May 2003, Larry Rosenman 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.... > record_values_new := record_values_new || '','' || > || quote_literal(NEW.netblock) || ''; Isn't the above an unterminated literal? Do you really need the last || '' part?
--On Tuesday, May 13, 2003 12:56:36 -0700 Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote: > > On Tue, 13 May 2003, Larry Rosenman 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.... > >> record_values_new := record_values_new || '','' || >> || quote_literal(NEW.netblock) || ''; > > Isn't the above an unterminated literal? Do you really need the last || > '' part? Eww. I'll bet your right. Damn Cut/paste and changing logic. :-). I'll go try that. LER -- 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
--On Tuesday, May 13, 2003 14:58:01 -0500 Larry Rosenman <ler@lerctr.org>
wrote:
>>> Ok, I'm blind or stupid, or quoting rules are biting my butt....
>>
>>> record_values_new := record_values_new || '','' ||
>>> || quote_literal(NEW.netblock) || '';
>>
>> Isn't the above an unterminated literal? Do you really need the last ||
>> '' part?
> Eww. I'll bet your right. Damn Cut/paste and changing logic. :-).
>
> I'll go try that.
Yep, that was it, now I get this, and I'm not sure how to deal with it...
netmaster=# insert into networks values('111.111.111.0/24');
WARNING: Error occurred while executing PL/pgSQL function
networks_trigger_log
WARNING: line 19 at assignment
ERROR: Function quote_literal(cidr) does not exist Unable to identify a function that satisfies the given
argument
types You may need to add explicit typecasts
netmaster=#
>
> LER
>
>
>
> --
> 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
>
>
--
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
On Tue, 13 May 2003, Larry Rosenman wrote:
>
>
> --On Tuesday, May 13, 2003 14:58:01 -0500 Larry Rosenman <ler@lerctr.org>
> wrote:
>
>
> >>> Ok, I'm blind or stupid, or quoting rules are biting my butt....
> >>
> >>> record_values_new := record_values_new || '','' ||
> >>> || quote_literal(NEW.netblock) || '';
> >>
> >> Isn't the above an unterminated literal? Do you really need the last ||
> >> '' part?
> > Eww. I'll bet your right. Damn Cut/paste and changing logic. :-).
> >
> > I'll go try that.
> Yep, that was it, now I get this, and I'm not sure how to deal with it...
>
> netmaster=# insert into networks values('111.111.111.0/24');
> WARNING: Error occurred while executing PL/pgSQL function
> networks_trigger_log
> WARNING: line 19 at assignment
> ERROR: Function quote_literal(cidr) does not exist
> Unable to identify a function that satisfies the given argument
> types
> You may need to add explicit typecasts
Ugh. cidr doesn't appear to cast to text. You may be able to go through
inet, but I'm not really sure because I haven't really used those types.
--On Tuesday, May 13, 2003 13:08:24 -0700 Stephan Szabo
<sszabo@megazone23.bigpanda.com> wrote:
> On Tue, 13 May 2003, Larry Rosenman wrote:
>
>>
>>
>> --On Tuesday, May 13, 2003 14:58:01 -0500 Larry Rosenman <ler@lerctr.org>
>> wrote:
>>
>>
>> >>> Ok, I'm blind or stupid, or quoting rules are biting my butt....
>> >>
>> >>> record_values_new := record_values_new || '','' ||
>> >>> || quote_literal(NEW.netblock) || '';
>> >>
>> >> Isn't the above an unterminated literal? Do you really need the last
>> >> || '' part?
>> > Eww. I'll bet your right. Damn Cut/paste and changing logic. :-).
>> >
>> > I'll go try that.
>> Yep, that was it, now I get this, and I'm not sure how to deal with it...
>>
>> netmaster=# insert into networks values('111.111.111.0/24');
>> WARNING: Error occurred while executing PL/pgSQL function
>> networks_trigger_log
>> WARNING: line 19 at assignment
>> ERROR: Function quote_literal(cidr) does not exist
>> Unable to identify a function that satisfies the given argument
>> types
>> You may need to add explicit typecasts
>
> Ugh. cidr doesn't appear to cast to text. You may be able to go through
> inet, but I'm not really sure because I haven't really used those types.
looks like I can, **BUT** is this the RIGHT thing? I suspect it SHOULD
cast to
text. IMNSHO.
Tom, Comments?
LER
>
--
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
Larry Rosenman <ler@lerctr.org> writes:
>> Ugh. cidr doesn't appear to cast to text. You may be able to go through
>> inet, but I'm not really sure because I haven't really used those types.
> looks like I can, **BUT** is this the RIGHT thing? I suspect it SHOULD
> cast to text. IMNSHO.
Probably. We have an inet->text cast, and the same function would work
for cidr->text. I think it's just an oversight that there's no such
entry in pg_cast. You could add one by hand ...
regards, tom lane
--On Tuesday, May 13, 2003 16:21:39 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Larry Rosenman <ler@lerctr.org> writes: >>> Ugh. cidr doesn't appear to cast to text. You may be able to go >>> through inet, but I'm not really sure because I haven't really used >>> those types. > >> looks like I can, **BUT** is this the RIGHT thing? I suspect it SHOULD >> cast to text. IMNSHO. > > Probably. We have an inet->text cast, and the same function would work > for cidr->text. I think it's just an oversight that there's no such > entry in pg_cast. You could add one by hand ... How would I do that? Also, this is what I wound up with for that line, which seems ugly, because it couldn't find a quote_literal(inet) without the ::text on it: quote_literal(NEW.netblock::inet::text) ; Is that right? > > regards, tom lane -- 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
Hi All, I am trying to create a function that will test if a given text value may safely be cast to numeric, returning the numeric cast or null if impossible. Is there a way to "catch" the "ERROR: Bad numeric input format" error? I ask that rather than about what the numeric format is for regex-style testing because if this works I may want to use a similar method to check if a given text value casts safely to other types, like date. Best, Randall
Josh Berkus <josh@agliodbs.com> writes:
>> 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.
Just FYI, I noticed the other day while testing some plpgsql changes
that the 7.3 plpgsql seems to double-count newlines appearing inside
quoted literals --- that is, if you have a syntax error appearing after
a multiline literal, the reported line number will be too large. This
seems to explain Larry's bizarre message, since most of the function
was being taken as a multiline literal.
The reason I noticed this was that the updated version counts correctly,
and thus gave different answers. I don't know exactly where the bug
really was --- the changes involved ripping out the use of yylineno
entirely, so I did not look to see just where the double counting had
been happening.
regards, tom lane
--On Tuesday, May 13, 2003 19:22:20 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Josh Berkus <josh@agliodbs.com> writes: >>> 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. > > Just FYI, I noticed the other day while testing some plpgsql changes > that the 7.3 plpgsql seems to double-count newlines appearing inside > quoted literals --- that is, if you have a syntax error appearing after > a multiline literal, the reported line number will be too large. This > seems to explain Larry's bizarre message, since most of the function > was being taken as a multiline literal. AH! Thank You for the explanation. (the quoting rules get weird, and I haven't done much PL/pgSQL coding (as if you couldn't tell).). > > The reason I noticed this was that the updated version counts correctly, > and thus gave different answers. I don't know exactly where the bug > really was --- the changes involved ripping out the use of yylineno > entirely, so I did not look to see just where the double counting had > been happening. Makes sense. Thanks again! > > regards, tom lane -- 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