Capture logging for stored procedure in Postgresql

Question

We are working on a reporting project where we are executing stored procedures to perform transformations on the source data and convert it into the desired format. The transformed data will be inserted into a different table. We would like to know what are the best practices that needs to be implemented in the stored procedures mainly for

To get the count of records processed from the source table. After the data is fetched from source table & transformations performed, it will be inserted into some intermediate table. In our case, we are fetching source from multiple tables and performing transformations. We have a ROW_COUNT() in mysql to get the rows inserted/ updated. I wanted to know the equivalent in postgres
In case of error during any of the process like insertion or updation how to capture the error details. We are currently using exception handler in the code. But is there any specific postgres functions or commands that can be used to capture the error codes etc.

Any additional information/best practices that can be included in the stored procedures to more clarity/efficieny, please advise

Any help on the above is much appreciated.

Jonathan Jacobson · Accepted Answer · 2020-09-02 05:13:31Z

2

Read this documentation part describing GET DIAGNOSTICS
Read this about SQLSTATE and GET STACKED DIAGNOSTICS

answered Sep 2, 2020 at 5:13

Jonathan Jacobson

1,52811 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2020-09-02 05:14:54Z

1

You can use GET STACKED DIAGNOSTICS to get all relevant information about an error in a PL/pgSQL exception handler. To handle the information somewhere else, you could persist it in a temporary table.

Concerning the row count, you could use count as a window function. But since you say that you are fetching the rows in the procedure, it would be cheaper to simply count them as you fetch them.

answered Sep 2, 2020 at 5:14

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Collectives™ on Stack Overflow

Capture logging for stored procedure in Postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related