http://postgres.cz/index.php?title=PostgreSQL_SQL_Tricks&feed=atom&action=history
PostgreSQL SQL Tricks - Historie editací
2024-03-28T14:38:14Z
Historie editací této stránky
MediaWiki 1.36.0
http://postgres.cz/index.php?title=PostgreSQL_SQL_Tricks&diff=1001&oldid=prev
PavelStehule v 5. 3. 2024, 14:33
2024-03-05T14:33:56Z
<p></p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="cs">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Starší verze</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Verze z 5. 3. 2024, 14:33</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l1">Řádek 1:</td>
<td colspan="2" class="diff-lineno">Řádek 1:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>SQL is a language where one task can be solved multiple ways with different efficiency. You can see effective use of PostgreSQL's possibilities on this page.</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>SQL is a language where one task can be solved multiple ways with different efficiency. You can see effective use of PostgreSQL's possibilities on this page.</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"><div style="padding:20px; float: right;"></del></div></td><td colspan="2"></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"><div style=" border: solid thin lightgrey; padding:10px;"></del></div></td><td colspan="2"></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"><htmlet>reklama</htmlet></del></div></td><td colspan="2"></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"></div></del></div></td><td colspan="2"></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"></div></del></div></td><td colspan="2"></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>[[PostgreSQL SQL Tricks III|Older tricks]]</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>[[PostgreSQL SQL Tricks III|Older tricks]]</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">==Processing PostgreSQL's logs in JSON format with jq==</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">Author: Pavel Stěhule</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">The JSON logs are not too readable, but using <code>jq</code> is simple:</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"><pre></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">cat postgresql-Sun.json | jq 'select(.error_severity=="ERROR").message'|sort -n | uniq -c</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> 1 "canceling statement due to user request"</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> 1 "column \"de.id\" must appear in the GROUP BY clause or be used in an aggregate function"</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> 1 "column reference \"modify_time\" is ambiguous"</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> 3 "column \"us.show_name\" must appear in the GROUP BY clause or be used in an aggregate function"</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> 24 "current transaction is aborted, commands ignored until end of transaction block"</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> 3 "deadlock detected"</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">cat postgresql-Sun.json | \</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> jq -r 'select(.error_severity=="ERROR") | [.timestamp, .user, .ps, .error_severity, .message ] | @csv' \</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">| pspg --csv</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></pre></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">==How to copy result to clipboard from psql==</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">Author: Pavel Stěhule</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">psql doesn't support clipboard. But there are some special applications that does it (wlclipboard, xclip, pbcopy). psql can use it, and then it is easy to redirect result of query to libre office.</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"><pre></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">select * from obce \g (format=csv) | cat > tmpfile.csv; libreoffice --view --calc tmpfile.csv "--infilter='Text - txt - csv (StarCalc)':44,34,0,1"; rm tmpfile.csv</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">select * from pg_class limit 10 \g (format=csv tuples_only=off csv_fieldsep='\t') | wl-copy -t application/x-libreoffice-tsvc</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">\set gclip '\\g (format=csv tuples_only=off csv_fieldsep=\'\\t\') | wl-copy -t application/x-libreoffice-tsvc'</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">select * from pg_class limit 10 :gclip</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></pre></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">==Arrays of jsonb==</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">Author: Pavel Stěhule</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">PostgreSQL supports arrays of any non array types. Access to fields is implemented by usage square brackets. Same syntax is used for access to fields in jsonb documents.</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">Because we use same syntax, we have problem when we want to set an field of array of json docs. Postgres doesn't supports arrays of arrays, so it cannot to use set of pair of brackets</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">to access to different kind of object. But there is a workaround based on usage of composite types.</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"><pre></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">CREATE TYPE jsonb_singleval AS (v jsonb);</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">DO $$</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> DECLARE a jsonb_singleval[];</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">BEGIN</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> a[1].v['x1'] = jsonb '100';</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> RAISE NOTICE 'first item %', a[1].v;</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">END; </ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">$$;</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">NOTICE: first item {"x1": 100}</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">DO</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></pre></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">==How to pass value from client side to server side==</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">Author: Daniel Verite</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">System variable <code>PGOPTION</code> can be used for custom variables (GUC):</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"><pre></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">$ env PGOPTIONS="-c os.user=$USER" psql -d postgres</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">psql (12.4 (Debian 12.4-1.pgdg90+1))</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">Type "help" for help.</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">postgres=> show "os.user";</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> os.user</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">---------</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> daniel</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">(1 row)</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></pre></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">případně s escapeovanými hodnotami:</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"><pre></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">$ env PGOPTIONS="-c os.user=user\\ is\\ $USER" psql -d postgres</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">psql (12.4 (Debian 12.4-1.pgdg90+1))</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">Type "help" for help.</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">postgres=> show "os.user";</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> os.user </ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">----------------</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> user is daniel</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">(1 row)</ins></div></td></tr>
<tr><td colspan="2"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></pre></ins></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Log all SQL statements generated by application==</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>==Log all SQL statements generated by application==</div></td></tr>
</table>
PavelStehule
http://postgres.cz/index.php?title=PostgreSQL_SQL_Tricks&diff=543&oldid=prev
imported>Pavel v 21. 12. 2016, 20:25
2016-12-21T20:25:34Z
<p></p>
<p><b>Nová stránka</b></p><div>SQL is a language where one task can be solved multiple ways with different efficiency. You can see effective use of PostgreSQL's possibilities on this page.<br />
<br />
<div style="padding:20px; float: right;"><br />
<div style=" border: solid thin lightgrey; padding:10px;"><br />
<htmlet>reklama</htmlet><br />
</div><br />
</div><br />
[[PostgreSQL SQL Tricks III|Older tricks]]<br />
<br />
<br />
==Log all SQL statements generated by application==<br />
Author: Pavel Stěhule<br />
<br />
You can enforce additional libpq connection string options with system variable <code>PGOPTIONS</code>. Next example ensure log all statements of pg_dump:<br />
<br />
<pre><br />
PGOPTIONS="-c log_min_duration_statement=0" pg_dump -s postgres > /dev/null<br />
</pre><br />
<br />
==Vacuum system tables==<br />
Author: Pavel Stěhule<br />
<pre><br />
psql -At -c "select 'VACUUM ANALYZE pg_catalog.'||table_name from information_schema.tables where table_schema = 'pg_catalog' and table_type <> 'VIEW'" db_test \<br />
| psql -S db_test<br />
</pre><br />
<br />
==Setting DETAIL and HINT fields in PLPythonu exception==<br />
Author: Pavel Stěhule<br />
<br />
PLPythonu doesn't allow to set all possible fields in PostgreSQL exception. There are a workaround for setting DETAIL and HINT:<br />
<pre><br />
postgres=# do $$<br />
x = plpy.SPIError('Nazdarek'); <br />
x.spidata = (100, "Some detail", "some hint", None, None); <br />
raise x;<br />
$$ language plpythonu;<br />
ERROR: plpy.SPIError: Nazdarek<br />
DETAIL: Some detail<br />
HINT: some hint<br />
CONTEXT: Traceback (most recent call last):<br />
PL/Python anonymous code block, line 4, in <module><br />
raise x;<br />
PL/Python anonymous code block<br />
</pre><br />
<br />
Note: PostgreSQL 9.6 has native support.<br />
==Pretty xml formating==<br />
Author: Pavel Stěhule (internet - xslt template)<br />
<br />
Extension <i>xml2</i> supports xslt transformations. With function <i>xslt_process</i> a pretty printing of any xml documenty pretty simple:<br />
<pre><br />
create or replace function xml_pretty(xml)<br />
returns xml as $$<br />
select xslt_process($1,<br />
'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><br />
<xsl:strip-space elements="*" /><br />
<xsl:output method="xml" indent="yes" /><br />
<xsl:template match="node() | @*"><br />
<xsl:copy><br />
<xsl:apply-templates select="node() | @*" /><br />
</xsl:copy><br />
</xsl:template><br />
</xsl:stylesheet>')::xml<br />
$$ language sql immutable strict;<br />
</pre><br />
Example:<br />
<pre><br />
postgres=# select xml_pretty('<root><a><b>some text</b></a></root>');<br />
xml_pretty <br />
----------------------<br />
<root> +<br />
<a> +<br />
<b>some text</b>+<br />
</a> +<br />
</root> +<br />
<br />
(1 row)<br />
</pre><br />
==Debugging communication between PostgreSQL and client==<br />
Author: Pavel Stěhule<br />
<br />
There is great tool for this purpose - [https://github.com/dalibo/pgshark pgShark].<br />
<pre><br />
unbuffer ./pgs-debug --host 172.30.34.72 -i lo --port 6432 | while read line; do echo `date +"%T.%3N"` $line; done | gzip > /mnt/ebs/pgsharklog.gz<br />
</pre> <br />
<br />
==Fast replacing a index of PRIMARY KEY constraint==<br />
Author: Petr Novak<br />
<br />
We can use a following steps, when we have replace a index for PRIMARY KEY constraint quickly:<br />
<pre><br />
BEGIN;<br />
CREATE UNIQUE INDEX CONCURRENTLY tab_pkey_idx2 ON tab(id);<br />
ALTER TABLE tab<br />
DROP CONSTRAINT tab_pkey CASCADE, <br />
ADD CONSTRAINT tab_pkey PRIMARY KEY USING INDEX tab_pkey_idx2;<br />
ALTER TABLE second_tab<br />
ADD CONSTRAINT second_tab_fkey FOREIGN KEY (tab_id) REFERENCES tab(id) NOT VALID;<br />
COMMIT;<br />
</pre><br />
Next step should be a FOREIGN KEY constraint validation.<br />
<br />
==Passing parameters from command line to DO statement==<br />
Author: Pavel Stehule<br />
<br />
PostgreSQL statement <code>DO</code> doesn't support parameters. But we can pass parameters there by psql and server side session parameters:<br />
<pre><br />
bash-4.1$ cat test.sh<br />
echo "<br />
set myvars.msgcount TO :'msgcount'; <br />
DO \$\$ <br />
BEGIN <br />
FOR i IN 1..current_setting('myvars.msgcount')::int LOOP <br />
RAISE NOTICE 'Hello';<br />
END LOOP; <br />
END \$\$" | psql postgres -v msgcount=$1<br />
<br />
bash-4.1$ sh test.sh 3<br />
SET<br />
Time: 0.341 ms<br />
NOTICE: Hello<br />
NOTICE: Hello<br />
NOTICE: Hello<br />
DO<br />
Time: 2.415 ms<br />
</pre><br />
<br />
==How to unnest an array with element indexes==<br />
Sometimes we need to unnest a array with indexes. There are more ways how to do it:<br />
<br />
Author: Pavel Stěhule (PostgreSQL 9.1)<br />
<pre><br />
CREATE OR REPLACE FUNCTION unnest_rownum(anyarray)<br />
RETURNS TABLE (id int, element anyelement) AS $$<br />
BEGIN<br />
id := 1;<br />
FOREACH element IN array $1<br />
LOOP<br />
RETURN NEXT;<br />
id := id + 1;<br />
END LOOP;<br />
RETURN;<br />
END<br />
$$ LANGUAGE plpgsql; <br />
<br />
postgres=# select * from unnest_rownum(ARRAY['A','B','C']);<br />
id | element<br />
----+---------<br />
1 | A<br />
2 | B<br />
3 | C<br />
(3 rows)<br />
</pre><br />
<br />
Author: Tom Lane (PostgreSQL 9.3)<br />
<pre><br />
SELECT i, arraycol[i]<br />
FROM tab,<br />
LATERAL generate_subscripts(arraycol, 1) as i;<br />
</pre><br />
<br />
9.4 supports clause WITH ORDINALITY:<br />
<pre><br />
postgres=# SELECT * FROM unnest(ARRAY['A','D','C']) WITH ORDINALITY;<br />
unnest | ordinality <br />
--------+------------<br />
A | 1<br />
D | 2<br />
C | 3<br />
(3 rows)<br />
</pre><br />
<br />
==Allow only one NULL in column==<br />
Author: Pavel Stěhule<br />
<br />
UNIQUE constraint ensures unique values in column(s). But NULL is not a comparable value and unique constraint ignores these values. Sometime we would to allow only one NULL in column. There is a simply solution based on partial index and functional (here only constant) index.<br />
<pre><br />
postgres=# CREATE TABLE omega(a int UNIQUE);<br />
CREATE TABLE<br />
<br />
postgres=# INSERT INTO omega VALUES(NULL);<br />
INSERT 0 1<br />
<br />
postgres=# INSERT INTO omega VALUES(NULL);<br />
INSERT 0 1<br />
<br />
postgres=# TRUNCATE omega;<br />
TRUNCATE TABLE<br />
<br />
postgres=# CREATE UNIQUE INDEX ON omega ((1)) WHERE a IS NULL;<br />
CREATE INDEX<br />
<br />
postgres=# INSERT INTO omega VALUES(NULL);<br />
INSERT 0 1<br />
<br />
postgres=# INSERT INTO omega VALUES(NULL);<br />
ERROR: duplicate key value violates unique constraint "omega_expr_idx"<br />
DETAIL: Key ((1))=(1) already exists.<br />
<br />
postgres=# UPDATE omega SET a = 10;<br />
UPDATE 1<br />
<br />
postgres=# INSERT INTO omega VALUES(NULL);<br />
INSERT 0 1<br />
Time: 7.601 ms<br />
</pre><br />
<br />
==Bytea to BLOB conversion==<br />
Author: unknown<br />
<br />
Store bytea string to LO object.<br />
<pre><br />
CREATE OR REPLACE FUNCTION make_lo(bytea)<br />
RETURNS oid AS $$<br />
DECLARE<br />
loid oid;<br />
fd integer;<br />
bytes integer;<br />
BEGIN<br />
loid := lo_creat(-1);<br />
fd := lo_open(loid, 131072);<br />
bytes := lowrite(fd, $1);<br />
IF (bytes != LENGTH($1)) THEN<br />
RAISE EXCEPTION 'Not all data copied to blob';<br />
END IF;<br />
PERFORM lo_close(fd);<br />
RETURN loid;<br />
END;<br />
$$ LANGUAGE plpgsql STRICT;<br />
</pre><br />
<br />
==Only one column can hold NULL==<br />
Author: Daniel Vérité<br />
<br />
How we can calculate how much columns hold a NULL value? Nice example of variadic function usage.<br />
<pre><br />
CREATE OR REPLACE FUNCTION public.null_count(VARIADIC anyarray)<br />
RETURNS integer<br />
LANGUAGE sql<br />
AS $function$<br />
SELECT sum(CASE WHEN v IS NULL THEN 1 ELSE 0 END)::int FROM unnest($1) g(v)<br />
$function$<br />
</pre><br />
Usage:<br />
<pre><br />
CREATE TABLE xxx(<br />
a int,<br />
b int,<br />
c int,<br />
CHECK (null_count(a,b,c) <= 1))<br />
</pre><br />
<br />
==Overwriting a application name in psql (for psql based script identification)==<br />
Author: Erik Rijkers<br />
<br />
Application_name is one of connection params, that we can attach to log event and later we can to identify a SQL statement producent. There are more possibilities how to do it (one is a example of connection string in url format).<br />
<pre><br />
[pavel@localhost ~]$ PGAPPNAME=splunk psql -c "select application_name from pg_stat_activity where pid = pg_backend_pid();" postgres<br />
application_name <br />
------------------<br />
splunk<br />
(1 row)<br />
<br />
[pavel@localhost ~]$ psql -c "select application_name from pg_stat_activity where pid = pg_backend_pid();" "dbname=postgres application_name=splunk"<br />
application_name <br />
------------------<br />
splunk<br />
(1 row)<br />
<br />
[pavel@localhost ~]$ psql -c "select application_name from pg_stat_activity where pid = pg_backend_pid();" postgresql://localhost/postgres?application_name=splunk<br />
application_name <br />
------------------<br />
splunk<br />
(1 row)<br />
</pre><br />
<br />
==Getting N unique most similar values with usage KNNsearch and pg_tgrm module==<br />
Author: Tom Lane<br />
<pre><br />
SELECT DISTINCT ON (village_name <-> 'Benešov') village_name, village_name<->'Benešov' <br />
FROM villages <br />
ORDER BY village_name <-> 'Benešov' <br />
LIMIT 10;<br />
<br />
village_name | ?column? <br />
-----------------------+----------<br />
Benešov | 0<br />
Benešovice | 0.416667<br />
Dolní Benešov | 0.428571<br />
Benešov u Semil | 0.5<br />
Benešov nad Černou | 0.578947<br />
Benešov nad Ploučnicí | 0.636364<br />
Benecko | 0.666667<br />
Benetice | 0.692308<br />
Bečov | 0.727273<br />
Bezkov | 0.75<br />
(10 rows)<br />
<br />
postgres=# EXPLAIN SELECT DISTINCT ON (village_name <-> 'Benešov') village_name, village_name<->'Benešov' <br />
FROM obce <br />
ORDER BY village_name <-> 'Benešov' <br />
LIMIT 10;<br />
QUERY PLAN <br />
-------------------------------------------------------------------------------------------------<br />
Limit (cost=0.00..2.76 rows=10 width=10)<br />
-> Unique (cost=0.00..1474.75 rows=5341 width=10)<br />
-> Index Scan using villages_village_name_idx on obce (cost=0.00..1433.14 rows=16644 width=10)<br />
Order By: (village_name <-> 'Benešov'::text)<br />
(4 rows)<br />
</pre><br />
<br />
==Using custom casting when you migrate from Oracle to PostgreSQL==<br />
Author: Pavel Stěhule<br />
<br />
Oracle doesn't support boolean data type, and developers use a varchar(1) or number(1) instead. It is a issue in PostgreSQL because this width is too less for storing a strings "true" or "false". But we can redefine a default casting (there is used a ugly hack - direct update of system tables under superuser rights). This solution is fast hack - changing to PostgreSQL's boolean is much better.<br />
<pre><br />
CREATE OR REPLACE FUNCTION public.boolcast_varchar(boolean)<br />
RETURNS character varying<br />
LANGUAGE sql<br />
AS $$<br />
SELECT CASE WHEN $1 THEN 't' ELSE 'f' END <br />
$$<br />
<br />
UPDATE pg_cast SET castfunc='boolcast_varchar'::regproc, <br />
castcontext='i' <br />
WHERE castsource=16 and casttarget=1043;<br />
<br />
CREATE TABLE test(a varchar(1));<br />
INSERT INTO test VALUES(true);<br />
</pre><br />
<br />
==JSON creating==<br />
Author: Merlin<br />
<br />
<pre><br />
select<br />
row_to_json(t1)<br />
from (<br />
select<br />
'joe' as username,<br />
(select project from (values(1, 'prj1')) as project(project_id,<br />
project_name)) as project<br />
) t1;<br />
</pre><br />
<br />
==A array's fields trimming==<br />
Author: a_horse_with_no_name<br />
<pre><br />
postgres=# select * from g;<br />
a <br />
--------------------------------<br />
{" ssss ssss","sss ssss "}<br />
{" ssss ssssaaaa "}<br />
(2 rows)<br />
<br />
postgres=# select row_number() over(), unnest(a) e<br />
from g;<br />
row_number | e <br />
------------+-----------------<br />
1 | ssss ssss<br />
1 | sss ssss <br />
2 | ssss ssssaaaa <br />
(3 rows)<br />
<br />
postgres=# select array_agg(trim(x.e)) <br />
from (select row_number() over() rn, unnest(a) e<br />
from g) x <br />
group by rn;<br />
array_agg <br />
---------------------------<br />
{"ssss ssss","sss ssss"}<br />
{"ssss ssssaaaa"}<br />
(2 rows)<br />
</pre><br />
Attention: This trick depends on imlementation - and should not work in future releases. If you use a 9.3 and newer PostgreSQL, use a LATERAL join instead.<br />
<br />
==Simply query result processing in bash==<br />
Author: Pavel Stěhule<br />
<br />
''psql'' has not complete macro language, but we can use ''bash'' and we can use features, that provides bash:<br />
<pre><br />
pavel ~ $ psql postgres -A -t --field-separator=" " \<br />
> -c "copy (select 1,'Ahoj Svete', i <br />
> from generate_series(1,3) g(i)) <br />
> to stdout delimiter ' '" | \<br />
> while read var1 var2 var3; <br />
> do <br />
> echo "a=$var1,b=$var2,c=$var3 "; <br />
> done<br />
a=1,b=Ahoj Svete,c=1 <br />
a=1,b=Ahoj Svete,c=2 <br />
a=1,b=Ahoj Svete,c=3 <br />
</pre><br />
<br />
Similar solution designed by Hubert Depesz Lubaczewski:<br />
<pre><br />
psql -qAtX -c "copy (select * from t) to stdout" | while IFS=$'\t' read -r a b c; do echo -e "a=[$a] b=[$b] c=[$c]"; done<br />
</pre><br />
<br />
==Glue comment to query==<br />
Author: Thomas Kellerer<br />
<br />
When you need append notice to query, you can use nested comment:<br />
<pre><br />
SELECT /* my comments, that I would to see in PostgreSQL log */<br />
a, b, c<br />
FROM mytab;<br />
</pre><br />
<br />
==Logout all connections==<br />
Author: Pavel Stěhule<br />
<br />
Execute query as user ''postgres'':<br />
<pre><br />
postgres=# select * from pg_stat_activity ;<br />
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | <br />
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+<br />
12894 | postgres | 21091 | 10 | postgres | psql | | | -1 | 2012-03-06 09:16:02.466983+01 |<br />
12894 | postgres | 21103 | 16384 | pavel | psql | | | -1 | 2012-03-06 09:17:02.827352+01 | <br />
12894 | postgres | 21104 | 16384 | pavel | psql | | | -1 | 2012-03-06 09:17:12.176979+01 | <br />
(3 rows)<br />
<br />
postgres=# select pg_terminate_backend(pid) <br />
from pg_stat_activity <br />
where pid <> pg_backend_pid() ;<br />
pg_terminate_backend <br />
----------------------<br />
t<br />
t<br />
(2 rows)<br />
<br />
postgres=# select * from pg_stat_activity ;<br />
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | <br />
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+<br />
12894 | postgres | 21091 | 10 | postgres | psql | | | -1 | 2012-03-06 09:16:02.466983+01 |<br />
(1 row)<br />
</pre><br />
<br />
==Taking first unlocked row from table==<br />
Sometimes we need first unlocked row (queue implementation). We can use following trick:<br />
<pre><br />
postgres=# select * from queue;<br />
id | processed | cmd <br />
----+-----------+-------<br />
1 | f | task1<br />
2 | f | task2<br />
3 | f | task2<br />
4 | f | task3<br />
(4 rows)<br />
</pre><br />
Usage:<br />
<pre><br />
-- consument1<br />
postgres=# begin;<br />
BEGIN<br />
postgres=# select * from queue where not processed and pg_try_advisory_xact_lock(id) for update limit 1;<br />
id | processed | cmd <br />
----+-----------+-------<br />
1 | f | task1<br />
(1 row)<br />
<br />
postgres=# update queue set processed = true where id = 1;<br />
UPDATE 1<br />
postgres=# commit;<br />
COMMIT<br />
<br />
-- consument2<br />
postgres=# begin;<br />
BEGIN<br />
postgres=# select * from queue where not processed and pg_try_advisory_xact_lock(id) for update limit 1;<br />
id | processed | cmd <br />
----+-----------+-------<br />
2 | f | task2<br />
(1 row)<br />
<br />
postgres=# update queue set processed = true where id = 2;<br />
UPDATE 1<br />
postgres=# commit;<br />
COMMIT<br />
</pre><br />
With advisory locks we can implement unblocked queue parallel processing.<br />
<br />
==Searching gaps in time series with window analytic functions==<br />
Source: Magnus Hagander http://blog.hagander.net/archives/203-Finding-gaps-in-partitioned-sequences.html<br />
<br />
Function ''lag'' can be used for any sorted series:<br />
<pre><br />
SELECT * FROM (<br />
SELECT<br />
gropid,<br />
year,<br />
month,<br />
seq, <br />
seq-lag(seq,1) OVER (PARTITION BY groupid, year, month ORDER BY seq) AS gap FROM mytable<br />
) AS t<br />
WHERE NOT (t.gap=1)<br />
ORDER BY groupid, year, month, seq<br />
</pre><br />
<br />
==Delete duplicate rows with window analytic functions==<br />
We can use a window function for very effective removing of duplicate rows:<br />
<pre><br />
DELETE FROM tab <br />
WHERE id IN (SELECT id <br />
FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), id <br />
FROM tab) x <br />
WHERE x.row_number > 1);<br />
</pre><br />
Some PostgreSQL's optimized version (with ctid):<br />
<pre><br />
DELETE FROM tab <br />
WHERE (ctid, tableoid) = ANY(ARRAY(SELECT (ctid, tableoid) <br />
FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), ctid <br />
FROM tab) x <br />
WHERE x.row_number > 1));<br />
</pre><br />
<br />
== LIKE to list of patterns==<br />
PostgreSQL cannot to use (I)LIKE together list of patterns (in conformance with ANSI SQL): <br />
<pre><br />
-- this is not valid<br />
WHERE somecolumn LIKE ('%some%','%someelse%') <br />
</pre><br />
But we can use arrays or regular expressions:<br />
<pre><br />
-- this is valid<br />
WHERE somecolumn ~~* any(array['%some%', '%someelse']));<br />
</pre><br />
Author: Tom Lane<br />
<br />
==Enforcing unique name for cursor in PL/pgSQL==<br />
PL/pgSQL cursors uses PostgreSQL SQL cursors. When we use PL/pgSQL cursor, then PL/pgSQL runtime creates SQL cursor with same name. This behave is unwanted sometimes. But when we know, so PL/pgSQL cursor is +/- text variable with SQL name, we can assign NULL to this variable. Then PL/pgSQL runtime cannot to use a predefined name and it will generate a unique name:<br />
<pre><br />
DECLARE<br />
mycursor CURSOR FOR SELECT * FROM int4_tbl WHERE f1 > id;<br />
newid INTEGER;<br />
out TEXT;<br />
BEGIN<br />
out := id::text || ' ';<br />
mycursor := null;<br />
OPEN mycursor;<br />
..<br />
</pre><br />
<br />
==Conversion BLOB to Bytea==<br />
Source: http://snipplr.com/view/62576/convert-oid-to-bytea/<br />
<pre><br />
CREATE OR REPLACE FUNCTION merge_oid(val oid)<br />
RETURNS bytea AS $$<br />
DECLARE <br />
merged bytea; <br />
arr bytea;<br />
BEGIN<br />
FOR arr IN SELECT DATA <br />
FROM pg_largeobject <br />
WHERE loid = val <br />
ORDER BY pageno <br />
LOOP<br />
IF merged IS NULL THEN<br />
merged := arr;<br />
ELSE<br />
merged := merged || arr;<br />
END IF;<br />
END LOOP;<br />
RETURN merged;<br />
END<br />
$$ LANGUAGE plpgsql;<br />
</pre><br />
<br />
==Function for decoding of url code==<br />
Author: Marti Raudsepp<br />
<pre><br />
CREATE OR REPLACE FUNCTION url_decode(input text) RETURNS text<br />
LANGUAGE plpgsql IMMUTABLE STRICT AS $$<br />
DECLARE<br />
bin bytea = '';<br />
byte text;<br />
BEGIN<br />
FOR byte IN (select (regexp_matches(input, '(%..|.)', 'g'))[1]) LOOP<br />
IF length(byte) = 3 THEN<br />
bin = bin || decode(substring(byte, 2, 2), 'hex');<br />
ELSE<br />
bin = bin || byte::bytea;<br />
END IF;<br />
END LOOP;<br />
RETURN convert_from(bin, 'utf8');<br />
END<br />
$$;<br />
</pre><br />
Usage:<br />
<pre><br />
ohs=# select url_decode('Hell%C3%B6%20World%21');<br />
url_decode <br />
──────────────<br />
Hellö World!<br />
(1 row)<br />
</pre><br />
Another version (by Marc Mamin)<br />
<pre><br />
CREATE OR REPLACE FUNCTION urldecode_arr(url text)<br />
RETURNS text AS $$<br />
BEGIN<br />
RETURN <br />
(WITH str AS (SELECT CASE WHEN $1 ~ '^%[0-9a-fA-F][0-9a-fA-F]' THEN array[''] END<br />
|| regexp_split_to_array ($1, '(%[0-9a-fA-F][0-9a-fA-F])+', 'i') plain,<br />
ARRAY(SELECT (regexp_matches ($1, '((?:%[0-9a-fA-F][0-9a-fA-F])+)', 'gi'))[1]) encoded)<br />
SELECT coalesce(string_agg(plain[i] || coalesce( convert_from(decode(replace(encoded[i], '%',''), 'hex'), 'utf8'), ''), ''), $1)<br />
FROM str,<br />
(SELECT generate_series(1, array_upper(encoded,1) + 2) i FROM str) blah);<br />
END<br />
$$ LANGUAGE plpgsql IMMUTABLE STRICT;<br />
</pre><br />
<br />
==Emacs's configuration for PLpgSQL syntax highlighting when function is edited from console==<br />
Author: Peter Eisentraut<br />
<br />
modify file .profile<br />
<pre><br />
PSQL_EDITOR=emacs;<br />
export PSQL_EDITOR.<br />
</pre><br />
<br />
modify file .emacs<br />
<pre><br />
<br />
(add-to-list 'auto-mode-alist<br />
'("/psql.edit.[0-9]+\\'" . sql-mode))<br />
</pre><br />
<br />
==Cast bytea to text==<br />
When we need cast content of bytea variable to adequate text value directly (content of bytea is char codes), then following function is solution:<br />
<pre><br />
CREATE OR REPLACE FUNCTION bytea_to_text(bytea) <br />
RETURNS text AS $$<br />
SELECT convert_from($1, current_setting('server_encoding'))<br />
$$ LANGUAGE sql;<br />
<br />
xxx=# SELECT bytea_to_text('žluťoučký kůň se napil žluté vody'::bytea);<br />
bytea_to_text <br />
───────────────────────────────────<br />
žluťoučký kůň se napil žluté vody<br />
(1 row)<br />
</pre><br />
<br />
==Simply age calculation from birth date==<br />
<pre><br />
postgres=# SELECT CURRENT_DATE;<br />
date <br />
------------<br />
2011-09-20<br />
(1 row)<br />
<br />
postgres=# SELECT EXTRACT(YEAR FROM age('1972-08-20'::date));<br />
date_part <br />
-----------<br />
39<br />
(1 row)<br />
</pre><br />
<br />
==Is current server master or slave?==<br />
When we need to identify server's state, then we can use a function:<br />
<pre><br />
SELECT pg_is_in_recovery();<br />
</pre><br />
Author: Simon Riggs<br />
<br />
==Dynamic modification some record's field==<br />
PL/pgSQL can modify record's field only when field specification is static. Some fast C libraries are generally available, but when we have no necessary rights, then we cannot use it. Then we can use function:<br />
<pre><br />
CREATE OR REPLACE FUNCTION x.setfield2(anyelement, text, text)<br />
RETURNS anyelement<br />
LANGUAGE plpgsql<br />
AS $function$<br />
DECLARE <br />
_name text;<br />
_values text[];<br />
_value text;<br />
_attnum int;<br />
BEGIN<br />
FOR _name, _attnum<br />
IN SELECT a.attname, a.attnum<br />
FROM pg_catalog.pg_attribute a <br />
WHERE a.attrelid = (SELECT typrelid<br />
FROM pg_type<br />
WHERE oid = pg_typeof($1)::oid) <br />
LOOP<br />
IF _name = $2 THEN<br />
_value := $3;<br />
ELSE<br />
EXECUTE 'SELECT (($1).' || quote_ident(_name) || ')::text' INTO _value USING $1;<br />
END IF;<br />
_values[_attnum] := COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', '');<br />
END LOOP;<br />
EXECUTE 'SELECT (' || pg_typeof($1)::text || '''(' || array_to_string(_values,',') || ')'').*' INTO $1; <br />
RETURN $1;<br />
END;<br />
$function$;<br />
<br />
postgres=# select setfield2(mypoint '(10,)', 'b', '33');<br />
setfield2 <br />
───────────<br />
(10,33)<br />
(1 row)<br />
<br />
Time: 9,480 ms<br />
</pre><br />
Author: Pavel Stěhule<br />
<br />
Erwin Brandstetter designed better (more simply, faster) solution:<br />
<pre><br />
-- Erwin 1<br />
CREATE OR REPLACE FUNCTION x.setfield3(anyelement, text, text)<br />
RETURNS anyelement<br />
AS $body$<br />
DECLARE<br />
_list text;<br />
<br />
BEGIN<br />
_list := (<br />
SELECT string_agg(x.fld, ',')<br />
FROM (<br />
SELECT CASE WHEN a.attname = $2<br />
THEN quote_literal($3)<br />
ELSE quote_ident(a.attname)<br />
END AS fld<br />
FROM pg_catalog.pg_attribute a <br />
WHERE a.attrelid = (SELECT typrelid<br />
FROM pg_type<br />
WHERE oid = pg_typeof($1)::oid) <br />
ORDER BY a.attnum<br />
) x<br />
);<br />
<br />
EXECUTE '<br />
SELECT ' || _list || '<br />
FROM (SELECT $1.*) x'<br />
USING $1<br />
INTO $1;<br />
<br />
RETURN $1;<br />
END;<br />
$body$ LANGUAGE plpgsql;<br />
</pre><br />
or modernised final version<br />
<pre><br />
CREATE FUNCTION f_setfield(INOUT _comp_val anyelement, _field text, _val text)<br />
RETURNS anyelement AS<br />
$func$<br />
BEGIN<br />
<br />
EXECUTE 'SELECT ' || array_to_string(ARRAY(<br />
SELECT CASE WHEN attname = _field<br />
THEN '$2'<br />
ELSE '($1).' || quote_ident(attname)<br />
END AS fld<br />
FROM pg_catalog.pg_attribute<br />
WHERE attrelid = pg_typeof(_comp_val)::text::regclass<br />
AND attnum > 0<br />
AND attisdropped = FALSE<br />
ORDER BY attnum<br />
), ',')<br />
USING _comp_val, _val<br />
INTO _comp_val;<br />
<br />
END<br />
$func$ LANGUAGE plpgsql;<br />
</pre><br />
<br />
==Iteration over RECORD variable inside trigger==<br />
Author: Tom Lane (for PostgreSQL 8.4 and higher)<br />
<pre><br />
CREATE OR REPLACE FUNCTION dynamic_trigger()<br />
RETURNS TRIGGER<br />
LANGUAGE plpgsql<br />
AS $$<br />
DECLARE<br />
ri RECORD;<br />
t TEXT;<br />
BEGIN<br />
RAISE NOTICE E'\n Operation: %\n Schema: %\n Table: %',<br />
TG_OP,<br />
TG_TABLE_SCHEMA,<br />
TG_TABLE_NAME;<br />
FOR ri IN<br />
SELECT ordinal_position, column_name, data_type<br />
FROM information_schema.columns<br />
WHERE<br />
table_schema = quote_ident(TG_TABLE_SCHEMA)<br />
AND table_name = quote_ident(TG_TABLE_NAME)<br />
ORDER BY ordinal_position<br />
LOOP<br />
EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO t USING NEW;<br />
RAISE NOTICE E'Column\n number: %\n name: %\n type: %\n value: %.',<br />
ri.ordinal_position,<br />
ri.column_name,<br />
ri.data_type,<br />
t;<br />
END LOOP;<br />
RETURN NEW;<br />
END; $$;<br />
</pre><br />
Note: This technique needs one dynamic SQL per one record's field - functions from PL Toolbox library or from Hstore contrib module are more effective.<br />
<br />
==Enforcing some chars in to_char function==<br />
Sometimes we needs to show some chars, that are same like special chars (has some semantic) from mask. Text in quotes (in format mask) is not interpreted and it is showed without changes:<br />
<pre><br />
postgres=# select to_char(current_timestamp,'YYYY-MM-DDTHH24:MI:SS');<br />
to_char <br />
-----------------------<br />
2010-02-07THH24:38:10<br />
(1 row)<br />
<br />
postgres=# select to_char(current_timestamp,'YYYY-MM-DD"T"HH24:MI:SS');<br />
to_char <br />
---------------------<br />
2010-02-07T07:38:22<br />
(1 row)<br />
</pre><br />
Author: Milen A. Radev<br />
<br />
==Barrier against to unwanted conversion char(n) to text==<br />
Almost all string functions have parameters of text type. PostgreSQL ensures automatic conversion from char(n) to varchar. But there are a few cases where this behave causes problems - mainly for older applications, where char(n) type was used (there is a clean advice - don't use this type). In some legacy application is comparison between char(n) type and literal constant with spaces and it doesn't work in PostgreSQL now:<br />
<pre><br />
SELECT ...<br />
WHERE substr(somecolumn,1,4) = 'ab ';<br />
</pre><br />
It doesn't work, because substr function has text parameter - and returns text and 'ab' is not equal to 'ab '. But we can overload substr function with char(n) datatype (it is possible because varchar and char uses same binary format) - and it doesn't do unwanted conversion from char(n) to text:<br />
<pre><br />
create or replace function substr(character, int, int) returns character as $$<br />
select substr($1::cstring::text,$2,$3)<br />
$$ language sql;<br />
</pre><br />
Tome Lane wrote more effective proposal:<br />
<pre><br />
create function substr(char,int,int) returns char<br />
strict immutable language internal as 'text_substr' ;<br />
</pre><br />
It is little bit dirty trick, but 100% functional.<br />
<pre><br />
postgres=# create table f(a character(5));<br />
CREATE TABLE<br />
postgres=# insert into f values('a'),('ab'),('abc');<br />
INSERT 0 3<br />
postgres=# select * from f;<br />
a<br />
-------<br />
a<br />
ab<br />
abc<br />
(3 rows)<br />
<br />
postgres=# select * from f where substr(a,1,3) = 'a ';<br />
a<br />
-------<br />
a<br />
(1 row)<br />
<br />
postgres=# select * from f where substr(a,1,3) = 'ab ';<br />
a<br />
-------<br />
ab<br />
(1 row)<br />
</pre><br />
<br />
==Get functions's DDL command==<br />
PostgreSQL allows simple way to get full source code of any function:<br />
<pre><br />
postgres=# select oid from pg_proc where proname = 'fu';<br />
oid <br />
-------<br />
16389<br />
(1 row)<br />
<br />
postgres=# select pg_get_functiondef(16389);<br />
pg_get_functiondef <br />
---------------------------------------------------------------------<br />
CREATE OR REPLACE FUNCTION public.fu(anyelement) <br />
RETURNS anyelement <br />
LANGUAGE plpgsql <br />
AS $function$<br />
begin <br />
raise notice '%', $1; <br />
return $1; <br />
end; $function$+<br />
<br />
(1 row)<br />
</pre><br />
==from Russian alphabet (Cyrillic) to ASCII==<br />
source: http://leha.papulovskiy.ru/blog/<br />
<pre><br />
CREATE OR REPLACE FUNCTION urltranslit(text) RETURNS text as $$<br />
SELECT<br />
regexp_replace(<br />
replace(<br />
replace(<br />
replace(<br />
replace(<br />
replace(<br />
replace(<br />
replace(<br />
translate(<br />
lower($1),<br />
'абвгдеёзийклмнопрстуфхыэъь',<br />
'abvgdeezijklmnoprstufhye'<br />
), 'ж', 'zh'<br />
), 'ц', 'ts'<br />
), 'ч', 'ch'<br />
), 'ш', 'sh'<br />
), 'щ', 'sch'<br />
), 'ю', 'yu'<br />
), 'я', 'ya'<br />
)<br />
,<br />
'[^a-z]+',<br />
'-',<br />
'g'<br />
)<br />
$$ LANGUAGE SQL;<br />
</pre><br />
<br />
[[PostgreSQL SQL Tricks III|Older tricks]]<br />
-----<br />
<center><br />
<htmlet>wide-banner</htmlet><br />
</center></div>
imported>Pavel