[PostgreSQL] Useful snippets

1. Query to get list of PostgreSQL functions in database

SELECT routines.routine_name, parameters.data_type, parameters.ordinal_position
FROM information_schema.routines
LEFT JOIN information_schema.parameters
    ON routines.specific_name=parameters.specific_name
WHERE routines.specific_schema='public'
ORDER BY routines.routine_name, parameters.ordinal_position;

 

2. Remove active session/transaction to a database

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'target_database' AND pid <> pg_backend_pid();

 

>>updating…