PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1038 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

# Name Goal Type Data source ▲ Last update License
801 User-defined derived tables Find user-defined views and materialized views. Pay attention to the outer join operations. One should use these if and only if there is a real need for them. Otherwise they just reduce performance. On the other hand, pay attention that outer join is used where it is logically needed. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
802 User-defined non-trigger routines without parameters Find user-defined non-trigger routines with no parameters. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
803 User-defined non-trigger SQL and PL/pgSQL routines This query retrieves a comprehensive list of user-defined routines (functions and procedures) written in SQL or PL/pgSQL. It explicitly filters the result set to exclude:

  • Trigger functions: Routines intended solely for use in triggers are omitted to focus on callable business logic.
  • System schemas: Routines located in system-managed namespaces (e.g., pg_catalog, information_schema) are excluded to isolate user-created code.
  • Extension routines.

The result provides an inventory of the application's explicit, callable database logic.		 General INFORMATION_SCHEMA+system catalog base tables 2025-11-30 08:41 MIT License View
804 User-defined routine execution privilege has been granted to PUBLIC You should follow the principle of least privilege and thus not have in your database user-defined routines that execution privilege is granted to PUBLIC, i.e., to all the database users now and in the future. By default, PostgreSQL gives routine execution privileges to PUBLIC. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
805 User-defined routines that have the same name as some system-defined routine. Avoid creating user-defined routines that have the same name as some system-defined routine because it may cause confusion. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
806 User-defined routines that implement UPSERT operation Find user-defioned routines that implement UPSERT operation. Make sure that it is consistent with the contracts of database operations. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
807 User-defined routines that produce a temporary table Find user user-defined routines that produce a temporary table General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
808 User-defined routines that read data Find user-defined routines that contain SELECT … FROM or PERFORM … FROM operations. PostgreSQL uses multiversion concurrency control (MVCC). Therefore, SELECTs do not block modifications and vice versa. One has to take steps to achieve correct behaviour of data access code. In addition, one should not ask data with multiple queries if it is possible to achieve the result with only one query. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
809 User-defined routines that use dynamic SQL to execute data manipulation statements Find user-defined routines that use dynamic SQL to execute data manipulation statements (SELECT, INSERT, UPDATE, DELETE). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
810 User-defined routines that use keyword DECLARE but do not declare anything Find user-defined routines that use keyword DECLARE but do not declare anything. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
811 User-defined routines that use md5 hash for other purposes than generating test data Find user-defined routines that use md5 hashes for the security purposes. Nowadays such hashes can be calculated too quickly and its use should be avoided at least for hashing passwords. Exclude routines that invoke both md5 function and generate_series function and are thus probably used to generate test data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
812 User-defined routines that use positional references to parameters Use parameter names instead of positional references to improve code evolvability. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
813 User-defined routines that use xmin hidden column Find routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
814 User-defined routines with dynamic SQL Find routines that use dynamic SQL. Make sure that dynamic SQL is indeed needed, i.e., the task cannot be solved with static SQL. Make sure that the routine is protected against attacks that use SQL injection method. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
815 User-defined routines with dynamic SQL that are potential targets of the SQL injection attack Find routines that have at least one input parameter, use dynamic SQL but do not escape the input arguments at all. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
816 User-defined routines with the same parameters (same name and type) regardless of the order of parameters Find routines with the same parameters (same name and type) regardless of the order of parameters. Make sure that there is no accidental duplication. The query helps users to group together routines that probably have related tasks. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
817 Username is not unique Find textual columns that potentially contain usernames (including columns that potentially contain e-mail addresses) that do not have a unique constraint or a unique index that involves only this column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
818 Using AFTER triggers to enforce constraints Do not let the system to do extra work. Checking a constraint with an AFTER trigger means that the trigger procedure will be executed after the data modification and if the check fails, then the system has to do extra work to roll back the changes. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
819 Using BEFORE triggers to log data changes Do not let the system to do extra work. Logging changes with a BEFORE trigger means extra work for rolling back the changes in case the logged data modification fails. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
820 Using conditionals to determine the returned value Use SQL language instead of PL/pgSQL where possible. Instead of using an IF statement, you can check as to whether the data modification succeeded or not by using the RETURNING clause in the data modification statement. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View