5

I have an error I'm trying to debug in a trigger function. In the Postgres log, I have the following:

2012-08-16 21:41:23 PDT ERROR:  Unknown period: D
2012-08-16 21:41:23 PDT CONTEXT:  PL/pgSQL function "handle_promotion_update" 
    line 60  at assignment
SQL statement "UPDATE promotion SET some_column = foo + 1
    WHERE id = NEW.promotion_id"
PL/pgSQL function "handle_new_reward" line 94 at SQL statement

So, it seems like my problem probably starts at "line 60"

My question, is where do the line numbers start? If I go in pgAdmin and look at the function declaration it has "helper" (like delete function) at the top. So, I don't think it's from there. But, does it include the function declaration? Or is it just from the line with BEGIN?

David S
  • 12,967
  • 12
  • 55
  • 93

1 Answers1

4

Line numbers that you see in the PostgreSQL server logs start at the line with the opening quotes of the function body. You have to add the lines before that to arrive at the absolute line number in the pgAdmin SQL editor.

Don't get confused: if you have errors while trying to create a function you get different line numbers: they refer to the executed script as a whole, while line numbers in runtime errors refer to the function body.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Hi Erwin... follow up here. Am I correct to assume that the "opening quote" is line 1? It's a 1-based numbering. Correct? – David S Dec 04 '13 at 18:42
  • @ErwinBrandstetter, What do you mean by "opening quote"? I've never seen a " at the beginning of my function before. – danjuggler Nov 16 '15 at 16:38
  • 2
    @danjuggler: The function body is a plain text string. It either starts with a single quote or, more commonly, with dollar-quoting. Details: http://stackoverflow.com/a/12320729/939860 or http://stackoverflow.com/a/12172353/939860 Aside: I never mentioned double quotes (`"`). – Erwin Brandstetter Nov 16 '15 at 17:05
  • 1
    `||||| ||||| ||||| |||| ||||| ||` <-- Number of times Erwin has schooled me. – markdsievers Oct 09 '19 at 02:15