Differences

This shows you the differences between two versions of the page.

--- dba:postgresql:postgresql_basic_querys [2023/09/29 07:59] – [List column index] dodger
+++ dba:postgresql:postgresql_basic_querys [2024/04/19 07:37] (current) – [Section: EMPTY] dodger
@@ Line 161: / Line 161: @@
 ===== list functions =====
+<code sql>
+\df
+</code>
+Or
+<code sql>
+SELECT n.nspname as "Schema",
+  p.proname as "Name",
+ CASE p.prokind
+  WHEN 'a' THEN 'agg'
+  WHEN 'w' THEN 'window'
+  WHEN 'p' THEN 'proc'
+  ELSE 'func'
+ END as "Type"
+FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
+WHERE pg_catalog.pg_function_is_visible(p.oid)
+      AND n.nspname <> 'pg_catalog'
+      AND n.nspname <> 'information_schema'
+ORDER BY 1, 2;
+</code>
+or complete:
+<code sql>
+SELECT n.nspname as "Schema",
+  p.proname as "Name",
+  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
+  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
+ CASE p.prokind
+  WHEN 'a' THEN 'agg'
+  WHEN 'w' THEN 'window'
+  WHEN 'p' THEN 'proc'
+  ELSE 'func'
+ END as "Type"
+FROM pg_catalog.pg_proc p
+     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
+WHERE pg_catalog.pg_function_is_visible(p.oid)
+      AND n.nspname <> 'pg_catalog'
+      AND n.nspname <> 'information_schema'
+ORDER BY 1, 2;
+</code>
+Note:
+  "Result data type" and "Argument data types" are normally multi-lined...
+==== Aggregate functions ====
 <code sql>
 \da
@@ Line 188: / Line 235: @@
 ===== list objects in schema =====
-Change ''<SCHEMA_NAME>'':
+Change ''<SCHEMA_NAME>'' :
 <code sql>
 select n.nspname as schema_name,
@@ Line 280: / Line 329: @@
 WHERE TABLE_NAME   = 'table_name'
 order by ordinal_position
+;
+</code>
+==== dba_tab_columns ====
+inefficient but who cares
+<code sql>
+SELECT * from
+(
+SELECT table_catalog DB_NAME,
+    table_schema SCHEMA_NAME,
+    table_name,
+    column_name,
+    ordinal_position,
+    data_type,
+    character_maximum_length,
+    column_default,
+    is_nullable
+FROM information_schema.columns
+order by ordinal_position
+) as dba_tab_columns
+WHERE COLUMN_NAME LIKE 'concession'
 ;
 </code>
@@ Line 298: / Line 368: @@
 ===== List column index =====
-aka ''dba_ind_columns'':
+==== dba_ind_columns ====
 <code sql>
@@ Line 343: / Line 414: @@
 ===== show object source =====
 Change ''FUNCTION_NAME'':
 <code sql>
 select prosrc from pg_proc where proname='FUNCTION_NAME'  ;
@@ Line 518: / Line 591: @@
 ===== Locks =====
+This query will list all locks:
 <code sql>
 SELECT psa.datid,
@@ Line 533: / Line 607: @@
 ;
 </code>
+This query will list all //blocking sessions//, see [[https://wiki.postgresql.org/wiki/Lock_Monitoring|official documentation]]:
+<code sql>
+SET application_name='%your_logical_name%';
+SELECT blocked_locks.pid     AS blocked_pid,
+         blocked_activity.usename  AS blocked_user,
+         blocking_locks.pid     AS blocking_pid,
+         blocking_activity.usename AS blocking_user,
+         blocked_activity.query    AS blocked_statement,
+         blocking_activity.query   AS current_statement_in_blocking_process,
+         blocked_activity.application_name AS blocked_application,
+         blocking_activity.application_name AS blocking_application
+   FROM  pg_catalog.pg_locks         blocked_locks
+    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
+    JOIN pg_catalog.pg_locks         blocking_locks
+        ON blocking_locks.locktype = blocked_locks.locktype
+        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
+        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
+        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
+        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
+        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
+        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
+        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
+        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
+        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
+        AND blocking_locks.pid != blocked_locks.pid
+    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
+   WHERE NOT blocked_locks.GRANTED;
+</code>
 ===== Change user password =====
 <code sql>
@@ Line 538: / Line 643: @@
 </code>
+===== dump/export all users with password =====
+<code bash>
+pg_dumpall --globals-only --file=all_roles_and_users.sql -h 127.0.0.1
+</code>
 ===== List extensions installed =====
@@ Line 775: / Line 885: @@
-====== Section: EMPTY ======
+====== Section: XML (tables) ======
+This is an example on how to use XMLTABLE function to extract data from a XML column:
+<code sql>
+    select
+        bdef.build_definition_id as build_definition_id,
+        tasks.task_definition_id as task_definition_id,
+        tasks.user_description as user_description,
+        tasks.is_enabled as is_enabled,
+        tasks.plugin_key as plugin_key,
+        tasks.finalising as finalising,
+        config_items.item_key as item_key,
+        config_items.item_value as item_value
+    FROM
+        build_definition bdef,
+        XMLTABLE('//taskDefinition' PASSING XMLPARSE(DOCUMENT xml_definition_data)
+                  COLUMNS
+                      task_definition_id integer PATH 'id',
+                      user_description text PATH 'userDescription',
+                      is_enabled boolean PATH 'isEnabled',
+                      plugin_key text PATH 'pluginKey',
+                      finalising boolean PATH 'finalising',
+                      config XML PATH 'config') AS tasks
+    left join XMLTABLE('//item' PASSING tasks.config COLUMNS item_key text PATH 'key',item_value text PATH 'value') AS config_items
+    on true
+    where xml_definition_data LIKE '%\/dip\/%'
+    order by 1
+;
+</code>
+====== Section: EMPTY ======
+<code sql>
-<code>
 </code>