Capture logging for stored procedure in Postgresql

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

1. 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
2. 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.

Answer

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.