Thread: pl/pgsql and error handling

pl/pgsql and error handling

From
Alex Hochberger
Date:
Is it possible to grab access to the actually user-friendly error  
message?  I have a bunch of files that come into my database, each  
slightly different.  The submitter fills out a form that records the  
info, and sticks it into a table.

If the file is malformed (wrong number of columns in a row) from a  
missing comma or something, I want to record the result to store in  
the database.  I assume that I should exception handle the COPY FROM  
line, and store the results, but I can't for the life of me figure out  
how to get the text like "invalid line on line 470352" or whatever  
psql shows.  Is that possible to obtain in plpgsql?

I've done extensive plpgsql programming before, but this is the first  
time I need it to validate the external data in question.

Alex


Re: pl/pgsql and error handling

From
Tom Lane
Date:
Alex Hochberger <alex@dsgi.us> writes:
> Is it possible to grab access to the actually user-friendly error  
> message?

Doesn't the SQLERRM variable do what you want?
        regards, tom lane


Re: pl/pgsql and error handling

From
Alex Hochberger
Date:
I do not know.  It's sounds like the Oracle PL/SQL behavior is what I  
want.  Does PostgreSQL's pl/pgsql have such a feature?  It's not in  
the documentation anywhere.  I'll check it from the office tomorrow.

Alex

On Feb 28, 2008, at 11:43 PM, Tom Lane wrote:

> Alex Hochberger <alex@dsgi.us> writes:
>> Is it possible to grab access to the actually user-friendly error
>> message?
>
> Doesn't the SQLERRM variable do what you want?
>
>             regards, tom lane
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org



Re: pl/pgsql and error handling

From
"Bart Degryse"
Date:
<a
href="http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING">www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING</a>
wouldbe the place to look.<br /><br />>>> Alex Hochberger <alex@dsgi.us> 2008-02-29 6:29 >>><br
/>Ido not know.  It's sounds like the Oracle PL/SQL behavior is what I  <br />want.  Does PostgreSQL's pl/pgsql have
sucha feature?  It's not in  <br />the documentation anywhere.  I'll check it from the office tomorrow.<br /><br
/>Alex<br/><br />On Feb 28, 2008, at 11:43 PM, Tom Lane wrote:<br /><br />> Alex Hochberger <alex@dsgi.us>
writes:<br/>>> Is it possible to grab access to the actually user-friendly error<br />>> message?<br
/>><br/>> Doesn't the SQLERRM variable do what you want?<br />><br />> regards, tom lane<br />><br
/>>---------------------------(end of  <br />> broadcast)---------------------------<br />> TIP 4: Have you
searchedour list archives?<br />><br />>               <a
href="http://archives.postgresql.org/">http://archives.postgresql.org</a><br/><br /><br
/>---------------------------(endof broadcast)---------------------------<br />TIP 7: You can help support the
PostgreSQLproject by donating at<br /><br />                <a
href="http://www.postgresql.org/about/donate">http://www.postgresql.org/about/donate</a><br/>