sql.md

PostgreSQL

prikazy

/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start
sudo systemctl start postgresql

SQL

INNER JOIN -- spoji podle klicu (default)  
OUTER JOIN -- prida nesparovane a doplni NULL sloupcema  
JOIN ... ON <podminka>  
JOIN ... USING (<nazev_sloupce>)  
NATURAL JOIN -- vybere pro USING spravny sloupec  
COPY <tablename> {TO|FROM} <filename>  

XMLSQL

zdroj

Functions

xmlcomment(text)

xmlconcat('', 'foo')
foo

xmlelement(
name name
[, xmlattributes(value [AS attname] [, ... ])]
[, content, ...]
)

xmlelement(
name foo,
xmlattributes(current_date as bar),
'cont', 'ent'
)
content

xmlforest(content [AS name] [, ...])
xmlforest('abc' AS foo, 123 AS bar)
abc123

xmlpi(name php, 'echo "hello world";')

xmlroot(
xml,
version text | no value
[, standalone yes|no|no value]
)
xmlroot(
xmlparse(document 'abc'),
version '1.0',
standalone yes
)

abc

xmlagg(xml)
CREATE TABLE test (y int, x xml);
INSERT INTO test VALUES (1, 'abc');
INSERT INTO test VALUES (2, '');
SELECT xmlagg(x) FROM test;

abc

SELECT xmlagg(x ORDER BY y DESC) FROM test;
abc

Predicates

xml IS DOCUMENT # correctect xml syntax

xmlexists(
'//town[text() = ''Toronto'']'
PASSING BY REF
'TorontoOttawa'
)
true

XPath

xpath syntax SELECT xpath(
'/my/text', /* path /
'
Prvni
Druhe
' / xml */
)
{Prvni,Druhe}