dba:postgresql:postgresql_basic_querys
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
dba:postgresql:postgresql_basic_querys [2024/03/15 09:16] – [list objects in schema] dodger | dba:postgresql:postgresql_basic_querys [2024/04/19 07:37] (current) – [Section: EMPTY] dodger | ||
---|---|---|---|
Line 414: | Line 414: | ||
===== show object source ===== | ===== show object source ===== | ||
Change '' | Change '' | ||
+ | |||
+ | |||
<code sql> | <code sql> | ||
select prosrc from pg_proc where proname=' | select prosrc from pg_proc where proname=' | ||
Line 883: | Line 885: | ||
- | ====== Section: | + | ====== Section: |
+ | 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('// | ||
+ | COLUMNS | ||
+ | task_definition_id integer PATH ' | ||
+ | user_description text PATH ' | ||
+ | is_enabled boolean PATH ' | ||
+ | plugin_key text PATH ' | ||
+ | finalising boolean PATH ' | ||
+ | config XML PATH ' | ||
+ | left join XMLTABLE('// | ||
+ | on true | ||
+ | where xml_definition_data LIKE ' | ||
+ | order by 1 | ||
+ | ; | ||
+ | </ | ||
+ | ====== Section: EMPTY ====== | ||
- | + | < | |
- | + | ||
- | + | ||
- | < | + | |
</ | </ | ||
dba/postgresql/postgresql_basic_querys.1710494202.txt.gz · Last modified: 2024/03/15 09:16 by dodger