CODEWITHSUNDEEP
databasepostgresqlloggingpgadmin
Joe DiNottra
Joe DiNottra

Reputation: 1018

How to get a SQL script log in PostgreSQL

As a team of developers we only have access to the dev database to make changes in tables, views, etc. When it comes to the test, stage, and production environments we send the "SQL script" to the DBAs, so they can run it in the corresponding higher environments.

Unfortunately, the DBAs only gives us the execution log for the last SQL statement in the script since "this is a limitation of pgAdmin". We are in the dark about how the rest of the script went, even in prod.

For example if the SQL script has 5 SQL queries, as in:

insert into ...
delete from ...
insert into ...
update ...
update ...

The DBA only sends us back the following single log line (that we assume corresponds to the last query):

Query returned successfully in 78 msec

Is it possible to get the success/fail status of each query in the script, along with the number of affected rows, using pgAdmin or other tool?

Upvotes: 0

Views: 55

Answers (1)

Dhinesh kumar P
Dhinesh kumar P

Reputation: 21

You can create a custom SQL script to execute each query in a block and capture the result. This method can return the status of each query and the number of rows affected. like bellow

INSERT INTO my_table (column1) VALUES ('value1') RETURNING id INTO v_result;
GET DIAGNOSTICS v_rows_affected = ROW_COUNT;
RAISE NOTICE 'Query 1 affected % rows', v_rows_affected;

Upvotes: 1

Related Questions