PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 997 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
561 Invalid character class PostgreSQL regular expressions do not have character classes word and letter. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:50 MIT License View
562 Duplication of case insensitivity specification in a regular expression Find regular expressions that use both case insensitive search operator ~* and case insensitivity modifier (?i). Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:49 MIT License View
563 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 2021-11-04 12:39 MIT License View
564 Routines that can be invoked with a variable number of arguments Find routines with a VARIADIC parameter. These are routines that take as input an undefined number of arguments where the argument that is an undefined number are all of the same type and are the last input arguments. General INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:37 MIT License View
565 SECURITY DEFINER procedures cannot end transactions You cannot use COMMIT and ROLLBACK in a SECURITY DEFINER procedure. Procedures appeared in PostgreSQL 11. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:12 MIT License View
566 Multiple selects from the same table in a routine Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of selecting different fields of the same row with different SELECT INTO statements one should try to do it with one statement. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:10 MIT License View
567 Explicit locking is probably not needed You do not need explicit locking (LOCK TABLE or SELECT … FOR UPDATE) in case of routines that only search some data but do not modify any data and do not raise any exception. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:08 MIT License View
568 No point to have in a procedure COMMIT without ROLLBACK or vice versa If you end transaction in a procedure, then there should be a possibility to either commit or rollback the transaction based on some condition. Procedures appeared in PostgreSQL 11. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:00 MIT License View
569 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 2021-11-04 11:58 MIT License View
570 The use of xmin hidden column in views and routines Find the number of views and materialized views that have a column with the xid type and the number of 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. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2021-11-04 11:56 MIT License View
571 PL/pgSQL routines that use a cursor Working with sets of rows rather than processing each row separately is more effective. General INFORMATION_SCHEMA+system catalog base tables 2021-11-04 11:54 MIT License View
572 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 2021-11-04 11:52 MIT License View
573 Procedures cannot have START TRANSACTION and SAVEPOINT You cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 11:50 MIT License View
574 The reference to a database operation is missing from a comment Find user-defined routines that comment does not contain a reference to a database operation that the routine implements. In case of routines that have been created based on the contracts of database operations, one should refer to the short identifier of the operation in the comment of the routine. The operation identifier must be in this case in the form OP, but other forms could be used as well. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 11:39 MIT License View
575 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 2021-11-04 11:30 MIT License View
576 There is no reason to use PL/pgSQL to write table functions Using PL/pgSQL may cause context switching between declarative SQL and procedural PL/pgSQL. Thus use PL/pgSQL only if you truly need some of its constructs. You can create table functions by using SQL. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 11:28 MIT License View
577 FOR UPDATE is not allowed with aggregate functions Implement explicit locking correctly. Instead of writing, for instance, SELECT Count(*) AS cnt FROM person WHERE person_id=1 FOR UPDATE; one has to write SELECT person_id FROM person WHERE person_id=1 FOR UPDATE; Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 11:23 MIT License View
578 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 2021-11-04 11:09 MIT License View
579 Functions that have transactional control Find functions that contain transactional control statements (BEGIN, START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT) in their body. PostgreSQL does not permit transaction control in functions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 10:50 MIT License View
580 SECURITY INVOKER routines that access data Find SECURITY INVOKER routines that read rows from a table, add rows to a table, update rows in a table, or delete rows from a table. Better to have for these purposes SECURITY DEFINER routines, which make it possible to give to the users privileges to only execute routines without having rights to access their underlying tables. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 10:44 MIT License View