The list of all the queries

All parameters with DEFAULT values

Query goal: Find parameters of user-defined routines that have a default value.
Notes about the query: The query does not consider the routines that are a part of an extension.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

SELECT 
specific_schema AS routine_schema,
regexp_replace(specific_name,'_[0-9]*$','') AS routine_name,
pg_get_function_identity_arguments(translate(substring(specific_name,'_[0-9]+$'),'_','')::int::oid) AS parameters,
parameter_name,
ordinal_position,
data_type,
parameter_default
FROM 
information_schema.parameters
WHERE specific_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) 
AND parameter_default IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM pg_catalog.pg_depend d inner join pg_catalog.pg_proc pc ON d.objid=pc.oid
WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND
pc.proname || '_' || pc.oid = parameters.specific_name)
ORDER BY routine_schema, routine_name, parameters, ordinal_position;

Collections where the query belongs to

Collection nameCollection description
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

Categories where the query belongs to

Category nameCategory description
Default valueQueries of this catergory provide information about the use of default values.
User-defined routinesQueries of this category provide information about the user-defined routines

The list of all the queries