dialect_postgres.go 40 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206
  1. // Copyright 2015 The Xorm Authors. All rights reserved.
  2. // Use of this source code is governed by a BSD-style
  3. // license that can be found in the LICENSE file.
  4. package xorm
  5. import (
  6. "errors"
  7. "fmt"
  8. "net/url"
  9. "sort"
  10. "strconv"
  11. "strings"
  12. "github.com/go-xorm/core"
  13. )
  14. // from http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html
  15. var (
  16. postgresReservedWords = map[string]bool{
  17. "A": true,
  18. "ABORT": true,
  19. "ABS": true,
  20. "ABSENT": true,
  21. "ABSOLUTE": true,
  22. "ACCESS": true,
  23. "ACCORDING": true,
  24. "ACTION": true,
  25. "ADA": true,
  26. "ADD": true,
  27. "ADMIN": true,
  28. "AFTER": true,
  29. "AGGREGATE": true,
  30. "ALL": true,
  31. "ALLOCATE": true,
  32. "ALSO": true,
  33. "ALTER": true,
  34. "ALWAYS": true,
  35. "ANALYSE": true,
  36. "ANALYZE": true,
  37. "AND": true,
  38. "ANY": true,
  39. "ARE": true,
  40. "ARRAY": true,
  41. "ARRAY_AGG": true,
  42. "ARRAY_MAX_CARDINALITY": true,
  43. "AS": true,
  44. "ASC": true,
  45. "ASENSITIVE": true,
  46. "ASSERTION": true,
  47. "ASSIGNMENT": true,
  48. "ASYMMETRIC": true,
  49. "AT": true,
  50. "ATOMIC": true,
  51. "ATTRIBUTE": true,
  52. "ATTRIBUTES": true,
  53. "AUTHORIZATION": true,
  54. "AVG": true,
  55. "BACKWARD": true,
  56. "BASE64": true,
  57. "BEFORE": true,
  58. "BEGIN": true,
  59. "BEGIN_FRAME": true,
  60. "BEGIN_PARTITION": true,
  61. "BERNOULLI": true,
  62. "BETWEEN": true,
  63. "BIGINT": true,
  64. "BINARY": true,
  65. "BIT": true,
  66. "BIT_LENGTH": true,
  67. "BLOB": true,
  68. "BLOCKED": true,
  69. "BOM": true,
  70. "BOOLEAN": true,
  71. "BOTH": true,
  72. "BREADTH": true,
  73. "BY": true,
  74. "C": true,
  75. "CACHE": true,
  76. "CALL": true,
  77. "CALLED": true,
  78. "CARDINALITY": true,
  79. "CASCADE": true,
  80. "CASCADED": true,
  81. "CASE": true,
  82. "CAST": true,
  83. "CATALOG": true,
  84. "CATALOG_NAME": true,
  85. "CEIL": true,
  86. "CEILING": true,
  87. "CHAIN": true,
  88. "CHAR": true,
  89. "CHARACTER": true,
  90. "CHARACTERISTICS": true,
  91. "CHARACTERS": true,
  92. "CHARACTER_LENGTH": true,
  93. "CHARACTER_SET_CATALOG": true,
  94. "CHARACTER_SET_NAME": true,
  95. "CHARACTER_SET_SCHEMA": true,
  96. "CHAR_LENGTH": true,
  97. "CHECK": true,
  98. "CHECKPOINT": true,
  99. "CLASS": true,
  100. "CLASS_ORIGIN": true,
  101. "CLOB": true,
  102. "CLOSE": true,
  103. "CLUSTER": true,
  104. "COALESCE": true,
  105. "COBOL": true,
  106. "COLLATE": true,
  107. "COLLATION": true,
  108. "COLLATION_CATALOG": true,
  109. "COLLATION_NAME": true,
  110. "COLLATION_SCHEMA": true,
  111. "COLLECT": true,
  112. "COLUMN": true,
  113. "COLUMNS": true,
  114. "COLUMN_NAME": true,
  115. "COMMAND_FUNCTION": true,
  116. "COMMAND_FUNCTION_CODE": true,
  117. "COMMENT": true,
  118. "COMMENTS": true,
  119. "COMMIT": true,
  120. "COMMITTED": true,
  121. "CONCURRENTLY": true,
  122. "CONDITION": true,
  123. "CONDITION_NUMBER": true,
  124. "CONFIGURATION": true,
  125. "CONNECT": true,
  126. "CONNECTION": true,
  127. "CONNECTION_NAME": true,
  128. "CONSTRAINT": true,
  129. "CONSTRAINTS": true,
  130. "CONSTRAINT_CATALOG": true,
  131. "CONSTRAINT_NAME": true,
  132. "CONSTRAINT_SCHEMA": true,
  133. "CONSTRUCTOR": true,
  134. "CONTAINS": true,
  135. "CONTENT": true,
  136. "CONTINUE": true,
  137. "CONTROL": true,
  138. "CONVERSION": true,
  139. "CONVERT": true,
  140. "COPY": true,
  141. "CORR": true,
  142. "CORRESPONDING": true,
  143. "COST": true,
  144. "COUNT": true,
  145. "COVAR_POP": true,
  146. "COVAR_SAMP": true,
  147. "CREATE": true,
  148. "CROSS": true,
  149. "CSV": true,
  150. "CUBE": true,
  151. "CUME_DIST": true,
  152. "CURRENT": true,
  153. "CURRENT_CATALOG": true,
  154. "CURRENT_DATE": true,
  155. "CURRENT_DEFAULT_TRANSFORM_GROUP": true,
  156. "CURRENT_PATH": true,
  157. "CURRENT_ROLE": true,
  158. "CURRENT_ROW": true,
  159. "CURRENT_SCHEMA": true,
  160. "CURRENT_TIME": true,
  161. "CURRENT_TIMESTAMP": true,
  162. "CURRENT_TRANSFORM_GROUP_FOR_TYPE": true,
  163. "CURRENT_USER": true,
  164. "CURSOR": true,
  165. "CURSOR_NAME": true,
  166. "CYCLE": true,
  167. "DATA": true,
  168. "DATABASE": true,
  169. "DATALINK": true,
  170. "DATE": true,
  171. "DATETIME_INTERVAL_CODE": true,
  172. "DATETIME_INTERVAL_PRECISION": true,
  173. "DAY": true,
  174. "DB": true,
  175. "DEALLOCATE": true,
  176. "DEC": true,
  177. "DECIMAL": true,
  178. "DECLARE": true,
  179. "DEFAULT": true,
  180. "DEFAULTS": true,
  181. "DEFERRABLE": true,
  182. "DEFERRED": true,
  183. "DEFINED": true,
  184. "DEFINER": true,
  185. "DEGREE": true,
  186. "DELETE": true,
  187. "DELIMITER": true,
  188. "DELIMITERS": true,
  189. "DENSE_RANK": true,
  190. "DEPTH": true,
  191. "DEREF": true,
  192. "DERIVED": true,
  193. "DESC": true,
  194. "DESCRIBE": true,
  195. "DESCRIPTOR": true,
  196. "DETERMINISTIC": true,
  197. "DIAGNOSTICS": true,
  198. "DICTIONARY": true,
  199. "DISABLE": true,
  200. "DISCARD": true,
  201. "DISCONNECT": true,
  202. "DISPATCH": true,
  203. "DISTINCT": true,
  204. "DLNEWCOPY": true,
  205. "DLPREVIOUSCOPY": true,
  206. "DLURLCOMPLETE": true,
  207. "DLURLCOMPLETEONLY": true,
  208. "DLURLCOMPLETEWRITE": true,
  209. "DLURLPATH": true,
  210. "DLURLPATHONLY": true,
  211. "DLURLPATHWRITE": true,
  212. "DLURLSCHEME": true,
  213. "DLURLSERVER": true,
  214. "DLVALUE": true,
  215. "DO": true,
  216. "DOCUMENT": true,
  217. "DOMAIN": true,
  218. "DOUBLE": true,
  219. "DROP": true,
  220. "DYNAMIC": true,
  221. "DYNAMIC_FUNCTION": true,
  222. "DYNAMIC_FUNCTION_CODE": true,
  223. "EACH": true,
  224. "ELEMENT": true,
  225. "ELSE": true,
  226. "EMPTY": true,
  227. "ENABLE": true,
  228. "ENCODING": true,
  229. "ENCRYPTED": true,
  230. "END": true,
  231. "END-EXEC": true,
  232. "END_FRAME": true,
  233. "END_PARTITION": true,
  234. "ENFORCED": true,
  235. "ENUM": true,
  236. "EQUALS": true,
  237. "ESCAPE": true,
  238. "EVENT": true,
  239. "EVERY": true,
  240. "EXCEPT": true,
  241. "EXCEPTION": true,
  242. "EXCLUDE": true,
  243. "EXCLUDING": true,
  244. "EXCLUSIVE": true,
  245. "EXEC": true,
  246. "EXECUTE": true,
  247. "EXISTS": true,
  248. "EXP": true,
  249. "EXPLAIN": true,
  250. "EXPRESSION": true,
  251. "EXTENSION": true,
  252. "EXTERNAL": true,
  253. "EXTRACT": true,
  254. "FALSE": true,
  255. "FAMILY": true,
  256. "FETCH": true,
  257. "FILE": true,
  258. "FILTER": true,
  259. "FINAL": true,
  260. "FIRST": true,
  261. "FIRST_VALUE": true,
  262. "FLAG": true,
  263. "FLOAT": true,
  264. "FLOOR": true,
  265. "FOLLOWING": true,
  266. "FOR": true,
  267. "FORCE": true,
  268. "FOREIGN": true,
  269. "FORTRAN": true,
  270. "FORWARD": true,
  271. "FOUND": true,
  272. "FRAME_ROW": true,
  273. "FREE": true,
  274. "FREEZE": true,
  275. "FROM": true,
  276. "FS": true,
  277. "FULL": true,
  278. "FUNCTION": true,
  279. "FUNCTIONS": true,
  280. "FUSION": true,
  281. "G": true,
  282. "GENERAL": true,
  283. "GENERATED": true,
  284. "GET": true,
  285. "GLOBAL": true,
  286. "GO": true,
  287. "GOTO": true,
  288. "GRANT": true,
  289. "GRANTED": true,
  290. "GREATEST": true,
  291. "GROUP": true,
  292. "GROUPING": true,
  293. "GROUPS": true,
  294. "HANDLER": true,
  295. "HAVING": true,
  296. "HEADER": true,
  297. "HEX": true,
  298. "HIERARCHY": true,
  299. "HOLD": true,
  300. "HOUR": true,
  301. "ID": true,
  302. "IDENTITY": true,
  303. "IF": true,
  304. "IGNORE": true,
  305. "ILIKE": true,
  306. "IMMEDIATE": true,
  307. "IMMEDIATELY": true,
  308. "IMMUTABLE": true,
  309. "IMPLEMENTATION": true,
  310. "IMPLICIT": true,
  311. "IMPORT": true,
  312. "IN": true,
  313. "INCLUDING": true,
  314. "INCREMENT": true,
  315. "INDENT": true,
  316. "INDEX": true,
  317. "INDEXES": true,
  318. "INDICATOR": true,
  319. "INHERIT": true,
  320. "INHERITS": true,
  321. "INITIALLY": true,
  322. "INLINE": true,
  323. "INNER": true,
  324. "INOUT": true,
  325. "INPUT": true,
  326. "INSENSITIVE": true,
  327. "INSERT": true,
  328. "INSTANCE": true,
  329. "INSTANTIABLE": true,
  330. "INSTEAD": true,
  331. "INT": true,
  332. "INTEGER": true,
  333. "INTEGRITY": true,
  334. "INTERSECT": true,
  335. "INTERSECTION": true,
  336. "INTERVAL": true,
  337. "INTO": true,
  338. "INVOKER": true,
  339. "IS": true,
  340. "ISNULL": true,
  341. "ISOLATION": true,
  342. "JOIN": true,
  343. "K": true,
  344. "KEY": true,
  345. "KEY_MEMBER": true,
  346. "KEY_TYPE": true,
  347. "LABEL": true,
  348. "LAG": true,
  349. "LANGUAGE": true,
  350. "LARGE": true,
  351. "LAST": true,
  352. "LAST_VALUE": true,
  353. "LATERAL": true,
  354. "LC_COLLATE": true,
  355. "LC_CTYPE": true,
  356. "LEAD": true,
  357. "LEADING": true,
  358. "LEAKPROOF": true,
  359. "LEAST": true,
  360. "LEFT": true,
  361. "LENGTH": true,
  362. "LEVEL": true,
  363. "LIBRARY": true,
  364. "LIKE": true,
  365. "LIKE_REGEX": true,
  366. "LIMIT": true,
  367. "LINK": true,
  368. "LISTEN": true,
  369. "LN": true,
  370. "LOAD": true,
  371. "LOCAL": true,
  372. "LOCALTIME": true,
  373. "LOCALTIMESTAMP": true,
  374. "LOCATION": true,
  375. "LOCATOR": true,
  376. "LOCK": true,
  377. "LOWER": true,
  378. "M": true,
  379. "MAP": true,
  380. "MAPPING": true,
  381. "MATCH": true,
  382. "MATCHED": true,
  383. "MATERIALIZED": true,
  384. "MAX": true,
  385. "MAXVALUE": true,
  386. "MAX_CARDINALITY": true,
  387. "MEMBER": true,
  388. "MERGE": true,
  389. "MESSAGE_LENGTH": true,
  390. "MESSAGE_OCTET_LENGTH": true,
  391. "MESSAGE_TEXT": true,
  392. "METHOD": true,
  393. "MIN": true,
  394. "MINUTE": true,
  395. "MINVALUE": true,
  396. "MOD": true,
  397. "MODE": true,
  398. "MODIFIES": true,
  399. "MODULE": true,
  400. "MONTH": true,
  401. "MORE": true,
  402. "MOVE": true,
  403. "MULTISET": true,
  404. "MUMPS": true,
  405. "NAME": true,
  406. "NAMES": true,
  407. "NAMESPACE": true,
  408. "NATIONAL": true,
  409. "NATURAL": true,
  410. "NCHAR": true,
  411. "NCLOB": true,
  412. "NESTING": true,
  413. "NEW": true,
  414. "NEXT": true,
  415. "NFC": true,
  416. "NFD": true,
  417. "NFKC": true,
  418. "NFKD": true,
  419. "NIL": true,
  420. "NO": true,
  421. "NONE": true,
  422. "NORMALIZE": true,
  423. "NORMALIZED": true,
  424. "NOT": true,
  425. "NOTHING": true,
  426. "NOTIFY": true,
  427. "NOTNULL": true,
  428. "NOWAIT": true,
  429. "NTH_VALUE": true,
  430. "NTILE": true,
  431. "NULL": true,
  432. "NULLABLE": true,
  433. "NULLIF": true,
  434. "NULLS": true,
  435. "NUMBER": true,
  436. "NUMERIC": true,
  437. "OBJECT": true,
  438. "OCCURRENCES_REGEX": true,
  439. "OCTETS": true,
  440. "OCTET_LENGTH": true,
  441. "OF": true,
  442. "OFF": true,
  443. "OFFSET": true,
  444. "OIDS": true,
  445. "OLD": true,
  446. "ON": true,
  447. "ONLY": true,
  448. "OPEN": true,
  449. "OPERATOR": true,
  450. "OPTION": true,
  451. "OPTIONS": true,
  452. "OR": true,
  453. "ORDER": true,
  454. "ORDERING": true,
  455. "ORDINALITY": true,
  456. "OTHERS": true,
  457. "OUT": true,
  458. "OUTER": true,
  459. "OUTPUT": true,
  460. "OVER": true,
  461. "OVERLAPS": true,
  462. "OVERLAY": true,
  463. "OVERRIDING": true,
  464. "OWNED": true,
  465. "OWNER": true,
  466. "P": true,
  467. "PAD": true,
  468. "PARAMETER": true,
  469. "PARAMETER_MODE": true,
  470. "PARAMETER_NAME": true,
  471. "PARAMETER_ORDINAL_POSITION": true,
  472. "PARAMETER_SPECIFIC_CATALOG": true,
  473. "PARAMETER_SPECIFIC_NAME": true,
  474. "PARAMETER_SPECIFIC_SCHEMA": true,
  475. "PARSER": true,
  476. "PARTIAL": true,
  477. "PARTITION": true,
  478. "PASCAL": true,
  479. "PASSING": true,
  480. "PASSTHROUGH": true,
  481. "PASSWORD": true,
  482. "PATH": true,
  483. "PERCENT": true,
  484. "PERCENTILE_CONT": true,
  485. "PERCENTILE_DISC": true,
  486. "PERCENT_RANK": true,
  487. "PERIOD": true,
  488. "PERMISSION": true,
  489. "PLACING": true,
  490. "PLANS": true,
  491. "PLI": true,
  492. "PORTION": true,
  493. "POSITION": true,
  494. "POSITION_REGEX": true,
  495. "POWER": true,
  496. "PRECEDES": true,
  497. "PRECEDING": true,
  498. "PRECISION": true,
  499. "PREPARE": true,
  500. "PREPARED": true,
  501. "PRESERVE": true,
  502. "PRIMARY": true,
  503. "PRIOR": true,
  504. "PRIVILEGES": true,
  505. "PROCEDURAL": true,
  506. "PROCEDURE": true,
  507. "PROGRAM": true,
  508. "PUBLIC": true,
  509. "QUOTE": true,
  510. "RANGE": true,
  511. "RANK": true,
  512. "READ": true,
  513. "READS": true,
  514. "REAL": true,
  515. "REASSIGN": true,
  516. "RECHECK": true,
  517. "RECOVERY": true,
  518. "RECURSIVE": true,
  519. "REF": true,
  520. "REFERENCES": true,
  521. "REFERENCING": true,
  522. "REFRESH": true,
  523. "REGR_AVGX": true,
  524. "REGR_AVGY": true,
  525. "REGR_COUNT": true,
  526. "REGR_INTERCEPT": true,
  527. "REGR_R2": true,
  528. "REGR_SLOPE": true,
  529. "REGR_SXX": true,
  530. "REGR_SXY": true,
  531. "REGR_SYY": true,
  532. "REINDEX": true,
  533. "RELATIVE": true,
  534. "RELEASE": true,
  535. "RENAME": true,
  536. "REPEATABLE": true,
  537. "REPLACE": true,
  538. "REPLICA": true,
  539. "REQUIRING": true,
  540. "RESET": true,
  541. "RESPECT": true,
  542. "RESTART": true,
  543. "RESTORE": true,
  544. "RESTRICT": true,
  545. "RESULT": true,
  546. "RETURN": true,
  547. "RETURNED_CARDINALITY": true,
  548. "RETURNED_LENGTH": true,
  549. "RETURNED_OCTET_LENGTH": true,
  550. "RETURNED_SQLSTATE": true,
  551. "RETURNING": true,
  552. "RETURNS": true,
  553. "REVOKE": true,
  554. "RIGHT": true,
  555. "ROLE": true,
  556. "ROLLBACK": true,
  557. "ROLLUP": true,
  558. "ROUTINE": true,
  559. "ROUTINE_CATALOG": true,
  560. "ROUTINE_NAME": true,
  561. "ROUTINE_SCHEMA": true,
  562. "ROW": true,
  563. "ROWS": true,
  564. "ROW_COUNT": true,
  565. "ROW_NUMBER": true,
  566. "RULE": true,
  567. "SAVEPOINT": true,
  568. "SCALE": true,
  569. "SCHEMA": true,
  570. "SCHEMA_NAME": true,
  571. "SCOPE": true,
  572. "SCOPE_CATALOG": true,
  573. "SCOPE_NAME": true,
  574. "SCOPE_SCHEMA": true,
  575. "SCROLL": true,
  576. "SEARCH": true,
  577. "SECOND": true,
  578. "SECTION": true,
  579. "SECURITY": true,
  580. "SELECT": true,
  581. "SELECTIVE": true,
  582. "SELF": true,
  583. "SENSITIVE": true,
  584. "SEQUENCE": true,
  585. "SEQUENCES": true,
  586. "SERIALIZABLE": true,
  587. "SERVER": true,
  588. "SERVER_NAME": true,
  589. "SESSION": true,
  590. "SESSION_USER": true,
  591. "SET": true,
  592. "SETOF": true,
  593. "SETS": true,
  594. "SHARE": true,
  595. "SHOW": true,
  596. "SIMILAR": true,
  597. "SIMPLE": true,
  598. "SIZE": true,
  599. "SMALLINT": true,
  600. "SNAPSHOT": true,
  601. "SOME": true,
  602. "SOURCE": true,
  603. "SPACE": true,
  604. "SPECIFIC": true,
  605. "SPECIFICTYPE": true,
  606. "SPECIFIC_NAME": true,
  607. "SQL": true,
  608. "SQLCODE": true,
  609. "SQLERROR": true,
  610. "SQLEXCEPTION": true,
  611. "SQLSTATE": true,
  612. "SQLWARNING": true,
  613. "SQRT": true,
  614. "STABLE": true,
  615. "STANDALONE": true,
  616. "START": true,
  617. "STATE": true,
  618. "STATEMENT": true,
  619. "STATIC": true,
  620. "STATISTICS": true,
  621. "STDDEV_POP": true,
  622. "STDDEV_SAMP": true,
  623. "STDIN": true,
  624. "STDOUT": true,
  625. "STORAGE": true,
  626. "STRICT": true,
  627. "STRIP": true,
  628. "STRUCTURE": true,
  629. "STYLE": true,
  630. "SUBCLASS_ORIGIN": true,
  631. "SUBMULTISET": true,
  632. "SUBSTRING": true,
  633. "SUBSTRING_REGEX": true,
  634. "SUCCEEDS": true,
  635. "SUM": true,
  636. "SYMMETRIC": true,
  637. "SYSID": true,
  638. "SYSTEM": true,
  639. "SYSTEM_TIME": true,
  640. "SYSTEM_USER": true,
  641. "T": true,
  642. "TABLE": true,
  643. "TABLES": true,
  644. "TABLESAMPLE": true,
  645. "TABLESPACE": true,
  646. "TABLE_NAME": true,
  647. "TEMP": true,
  648. "TEMPLATE": true,
  649. "TEMPORARY": true,
  650. "TEXT": true,
  651. "THEN": true,
  652. "TIES": true,
  653. "TIME": true,
  654. "TIMESTAMP": true,
  655. "TIMEZONE_HOUR": true,
  656. "TIMEZONE_MINUTE": true,
  657. "TO": true,
  658. "TOKEN": true,
  659. "TOP_LEVEL_COUNT": true,
  660. "TRAILING": true,
  661. "TRANSACTION": true,
  662. "TRANSACTIONS_COMMITTED": true,
  663. "TRANSACTIONS_ROLLED_BACK": true,
  664. "TRANSACTION_ACTIVE": true,
  665. "TRANSFORM": true,
  666. "TRANSFORMS": true,
  667. "TRANSLATE": true,
  668. "TRANSLATE_REGEX": true,
  669. "TRANSLATION": true,
  670. "TREAT": true,
  671. "TRIGGER": true,
  672. "TRIGGER_CATALOG": true,
  673. "TRIGGER_NAME": true,
  674. "TRIGGER_SCHEMA": true,
  675. "TRIM": true,
  676. "TRIM_ARRAY": true,
  677. "TRUE": true,
  678. "TRUNCATE": true,
  679. "TRUSTED": true,
  680. "TYPE": true,
  681. "TYPES": true,
  682. "UESCAPE": true,
  683. "UNBOUNDED": true,
  684. "UNCOMMITTED": true,
  685. "UNDER": true,
  686. "UNENCRYPTED": true,
  687. "UNION": true,
  688. "UNIQUE": true,
  689. "UNKNOWN": true,
  690. "UNLINK": true,
  691. "UNLISTEN": true,
  692. "UNLOGGED": true,
  693. "UNNAMED": true,
  694. "UNNEST": true,
  695. "UNTIL": true,
  696. "UNTYPED": true,
  697. "UPDATE": true,
  698. "UPPER": true,
  699. "URI": true,
  700. "USAGE": true,
  701. "USER": true,
  702. "USER_DEFINED_TYPE_CATALOG": true,
  703. "USER_DEFINED_TYPE_CODE": true,
  704. "USER_DEFINED_TYPE_NAME": true,
  705. "USER_DEFINED_TYPE_SCHEMA": true,
  706. "USING": true,
  707. "VACUUM": true,
  708. "VALID": true,
  709. "VALIDATE": true,
  710. "VALIDATOR": true,
  711. "VALUE": true,
  712. "VALUES": true,
  713. "VALUE_OF": true,
  714. "VARBINARY": true,
  715. "VARCHAR": true,
  716. "VARIADIC": true,
  717. "VARYING": true,
  718. "VAR_POP": true,
  719. "VAR_SAMP": true,
  720. "VERBOSE": true,
  721. "VERSION": true,
  722. "VERSIONING": true,
  723. "VIEW": true,
  724. "VOLATILE": true,
  725. "WHEN": true,
  726. "WHENEVER": true,
  727. "WHERE": true,
  728. "WHITESPACE": true,
  729. "WIDTH_BUCKET": true,
  730. "WINDOW": true,
  731. "WITH": true,
  732. "WITHIN": true,
  733. "WITHOUT": true,
  734. "WORK": true,
  735. "WRAPPER": true,
  736. "WRITE": true,
  737. "XML": true,
  738. "XMLAGG": true,
  739. "XMLATTRIBUTES": true,
  740. "XMLBINARY": true,
  741. "XMLCAST": true,
  742. "XMLCOMMENT": true,
  743. "XMLCONCAT": true,
  744. "XMLDECLARATION": true,
  745. "XMLDOCUMENT": true,
  746. "XMLELEMENT": true,
  747. "XMLEXISTS": true,
  748. "XMLFOREST": true,
  749. "XMLITERATE": true,
  750. "XMLNAMESPACES": true,
  751. "XMLPARSE": true,
  752. "XMLPI": true,
  753. "XMLQUERY": true,
  754. "XMLROOT": true,
  755. "XMLSCHEMA": true,
  756. "XMLSERIALIZE": true,
  757. "XMLTABLE": true,
  758. "XMLTEXT": true,
  759. "XMLVALIDATE": true,
  760. "YEAR": true,
  761. "YES": true,
  762. "ZONE": true,
  763. }
  764. )
  765. type postgres struct {
  766. core.Base
  767. }
  768. func (db *postgres) Init(d *core.DB, uri *core.Uri, drivername, dataSourceName string) error {
  769. return db.Base.Init(d, db, uri, drivername, dataSourceName)
  770. }
  771. func (db *postgres) SqlType(c *core.Column) string {
  772. var res string
  773. switch t := c.SQLType.Name; t {
  774. case core.TinyInt:
  775. res = core.SmallInt
  776. return res
  777. case core.MediumInt, core.Int, core.Integer:
  778. if c.IsAutoIncrement {
  779. return core.Serial
  780. }
  781. return core.Integer
  782. case core.BigInt:
  783. if c.IsAutoIncrement {
  784. return core.BigSerial
  785. }
  786. return core.BigInt
  787. case core.Serial, core.BigSerial:
  788. c.IsAutoIncrement = true
  789. c.Nullable = false
  790. res = t
  791. case core.Binary, core.VarBinary:
  792. return core.Bytea
  793. case core.DateTime:
  794. res = core.TimeStamp
  795. case core.TimeStampz:
  796. return "timestamp with time zone"
  797. case core.Float:
  798. res = core.Real
  799. case core.TinyText, core.MediumText, core.LongText:
  800. res = core.Text
  801. case core.NVarchar:
  802. res = core.Varchar
  803. case core.Uuid:
  804. res = core.Uuid
  805. case core.Blob, core.TinyBlob, core.MediumBlob, core.LongBlob:
  806. return core.Bytea
  807. case core.Double:
  808. return "DOUBLE PRECISION"
  809. default:
  810. if c.IsAutoIncrement {
  811. return core.Serial
  812. }
  813. res = t
  814. }
  815. hasLen1 := (c.Length > 0)
  816. hasLen2 := (c.Length2 > 0)
  817. if hasLen2 {
  818. res += "(" + strconv.Itoa(c.Length) + "," + strconv.Itoa(c.Length2) + ")"
  819. } else if hasLen1 {
  820. res += "(" + strconv.Itoa(c.Length) + ")"
  821. }
  822. return res
  823. }
  824. func (db *postgres) SupportInsertMany() bool {
  825. return true
  826. }
  827. func (db *postgres) IsReserved(name string) bool {
  828. _, ok := postgresReservedWords[name]
  829. return ok
  830. }
  831. func (db *postgres) Quote(name string) string {
  832. name = strings.Replace(name, ".", `"."`, -1)
  833. return "\"" + name + "\""
  834. }
  835. func (db *postgres) QuoteStr() string {
  836. return "\""
  837. }
  838. func (db *postgres) AutoIncrStr() string {
  839. return ""
  840. }
  841. func (db *postgres) SupportEngine() bool {
  842. return false
  843. }
  844. func (db *postgres) SupportCharset() bool {
  845. return false
  846. }
  847. func (db *postgres) IndexOnTable() bool {
  848. return false
  849. }
  850. func (db *postgres) IndexCheckSql(tableName, idxName string) (string, []interface{}) {
  851. args := []interface{}{tableName, idxName}
  852. return `SELECT indexname FROM pg_indexes ` +
  853. `WHERE tablename = ? AND indexname = ?`, args
  854. }
  855. func (db *postgres) TableCheckSql(tableName string) (string, []interface{}) {
  856. args := []interface{}{tableName}
  857. return `SELECT tablename FROM pg_tables WHERE tablename = ?`, args
  858. }
  859. /*func (db *postgres) ColumnCheckSql(tableName, colName string) (string, []interface{}) {
  860. args := []interface{}{tableName, colName}
  861. return "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ?" +
  862. " AND column_name = ?", args
  863. }*/
  864. func (db *postgres) ModifyColumnSql(tableName string, col *core.Column) string {
  865. return fmt.Sprintf("alter table %s ALTER COLUMN %s TYPE %s",
  866. tableName, col.Name, db.SqlType(col))
  867. }
  868. func (db *postgres) DropIndexSql(tableName string, index *core.Index) string {
  869. //var unique string
  870. quote := db.Quote
  871. idxName := index.Name
  872. if !strings.HasPrefix(idxName, "UQE_") &&
  873. !strings.HasPrefix(idxName, "IDX_") {
  874. if index.Type == core.UniqueType {
  875. idxName = fmt.Sprintf("UQE_%v_%v", tableName, index.Name)
  876. } else {
  877. idxName = fmt.Sprintf("IDX_%v_%v", tableName, index.Name)
  878. }
  879. }
  880. return fmt.Sprintf("DROP INDEX %v", quote(idxName))
  881. }
  882. func (db *postgres) IsColumnExist(tableName, colName string) (bool, error) {
  883. args := []interface{}{tableName, colName}
  884. query := "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = $1" +
  885. " AND column_name = $2"
  886. db.LogSQL(query, args)
  887. rows, err := db.DB().Query(query, args...)
  888. if err != nil {
  889. return false, err
  890. }
  891. defer rows.Close()
  892. return rows.Next(), nil
  893. }
  894. func (db *postgres) GetColumns(tableName string) ([]string, map[string]*core.Column, error) {
  895. // FIXME: the schema should be replaced by user custom's
  896. args := []interface{}{tableName, "public"}
  897. s := `SELECT column_name, column_default, is_nullable, data_type, character_maximum_length, numeric_precision, numeric_precision_radix ,
  898. CASE WHEN p.contype = 'p' THEN true ELSE false END AS primarykey,
  899. CASE WHEN p.contype = 'u' THEN true ELSE false END AS uniquekey
  900. FROM pg_attribute f
  901. JOIN pg_class c ON c.oid = f.attrelid JOIN pg_type t ON t.oid = f.atttypid
  902. LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
  903. LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  904. LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
  905. LEFT JOIN pg_class AS g ON p.confrelid = g.oid
  906. LEFT JOIN INFORMATION_SCHEMA.COLUMNS s ON s.column_name=f.attname AND c.relname=s.table_name
  907. WHERE c.relkind = 'r'::char AND c.relname = $1 AND s.table_schema = $2 AND f.attnum > 0 ORDER BY f.attnum;`
  908. db.LogSQL(s, args)
  909. rows, err := db.DB().Query(s, args...)
  910. if err != nil {
  911. return nil, nil, err
  912. }
  913. defer rows.Close()
  914. cols := make(map[string]*core.Column)
  915. colSeq := make([]string, 0)
  916. for rows.Next() {
  917. col := new(core.Column)
  918. col.Indexes = make(map[string]int)
  919. var colName, isNullable, dataType string
  920. var maxLenStr, colDefault, numPrecision, numRadix *string
  921. var isPK, isUnique bool
  922. err = rows.Scan(&colName, &colDefault, &isNullable, &dataType, &maxLenStr, &numPrecision, &numRadix, &isPK, &isUnique)
  923. if err != nil {
  924. return nil, nil, err
  925. }
  926. //fmt.Println(args, colName, isNullable, dataType, maxLenStr, colDefault, numPrecision, numRadix, isPK, isUnique)
  927. var maxLen int
  928. if maxLenStr != nil {
  929. maxLen, err = strconv.Atoi(*maxLenStr)
  930. if err != nil {
  931. return nil, nil, err
  932. }
  933. }
  934. col.Name = strings.Trim(colName, `" `)
  935. if colDefault != nil || isPK {
  936. if isPK {
  937. col.IsPrimaryKey = true
  938. } else {
  939. col.Default = *colDefault
  940. }
  941. }
  942. if colDefault != nil && strings.HasPrefix(*colDefault, "nextval(") {
  943. col.IsAutoIncrement = true
  944. }
  945. col.Nullable = (isNullable == "YES")
  946. switch dataType {
  947. case "character varying", "character":
  948. col.SQLType = core.SQLType{Name: core.Varchar, DefaultLength: 0, DefaultLength2: 0}
  949. case "timestamp without time zone":
  950. col.SQLType = core.SQLType{Name: core.DateTime, DefaultLength: 0, DefaultLength2: 0}
  951. case "timestamp with time zone":
  952. col.SQLType = core.SQLType{Name: core.TimeStampz, DefaultLength: 0, DefaultLength2: 0}
  953. case "double precision":
  954. col.SQLType = core.SQLType{Name: core.Double, DefaultLength: 0, DefaultLength2: 0}
  955. case "boolean":
  956. col.SQLType = core.SQLType{Name: core.Bool, DefaultLength: 0, DefaultLength2: 0}
  957. case "time without time zone":
  958. col.SQLType = core.SQLType{Name: core.Time, DefaultLength: 0, DefaultLength2: 0}
  959. case "oid":
  960. col.SQLType = core.SQLType{Name: core.BigInt, DefaultLength: 0, DefaultLength2: 0}
  961. default:
  962. col.SQLType = core.SQLType{Name: strings.ToUpper(dataType), DefaultLength: 0, DefaultLength2: 0}
  963. }
  964. if _, ok := core.SqlTypes[col.SQLType.Name]; !ok {
  965. return nil, nil, fmt.Errorf("Unknown colType: %v", dataType)
  966. }
  967. col.Length = maxLen
  968. if col.SQLType.IsText() || col.SQLType.IsTime() {
  969. if col.Default != "" {
  970. col.Default = "'" + col.Default + "'"
  971. } else {
  972. if col.DefaultIsEmpty {
  973. col.Default = "''"
  974. }
  975. }
  976. }
  977. cols[col.Name] = col
  978. colSeq = append(colSeq, col.Name)
  979. }
  980. return colSeq, cols, nil
  981. }
  982. func (db *postgres) GetTables() ([]*core.Table, error) {
  983. // FIXME: replace public to user customrize schema
  984. args := []interface{}{"public"}
  985. s := fmt.Sprintf("SELECT tablename FROM pg_tables WHERE schemaname = $1")
  986. db.LogSQL(s, args)
  987. rows, err := db.DB().Query(s, args...)
  988. if err != nil {
  989. return nil, err
  990. }
  991. defer rows.Close()
  992. tables := make([]*core.Table, 0)
  993. for rows.Next() {
  994. table := core.NewEmptyTable()
  995. var name string
  996. err = rows.Scan(&name)
  997. if err != nil {
  998. return nil, err
  999. }
  1000. table.Name = name
  1001. tables = append(tables, table)
  1002. }
  1003. return tables, nil
  1004. }
  1005. func (db *postgres) GetIndexes(tableName string) (map[string]*core.Index, error) {
  1006. // FIXME: replace the public schema to user specify schema
  1007. args := []interface{}{"public", tableName}
  1008. s := fmt.Sprintf("SELECT indexname, indexdef FROM pg_indexes WHERE schemaname=$1 AND tablename=$2")
  1009. db.LogSQL(s, args)
  1010. rows, err := db.DB().Query(s, args...)
  1011. if err != nil {
  1012. return nil, err
  1013. }
  1014. defer rows.Close()
  1015. indexes := make(map[string]*core.Index, 0)
  1016. for rows.Next() {
  1017. var indexType int
  1018. var indexName, indexdef string
  1019. var colNames []string
  1020. err = rows.Scan(&indexName, &indexdef)
  1021. if err != nil {
  1022. return nil, err
  1023. }
  1024. indexName = strings.Trim(indexName, `" `)
  1025. if strings.HasSuffix(indexName, "_pkey") {
  1026. continue
  1027. }
  1028. if strings.HasPrefix(indexdef, "CREATE UNIQUE INDEX") {
  1029. indexType = core.UniqueType
  1030. } else {
  1031. indexType = core.IndexType
  1032. }
  1033. cs := strings.Split(indexdef, "(")
  1034. colNames = strings.Split(cs[1][0:len(cs[1])-1], ",")
  1035. var isRegular bool
  1036. if strings.HasPrefix(indexName, "IDX_"+tableName) || strings.HasPrefix(indexName, "UQE_"+tableName) {
  1037. newIdxName := indexName[5+len(tableName):]
  1038. isRegular = true
  1039. if newIdxName != "" {
  1040. indexName = newIdxName
  1041. }
  1042. }
  1043. index := &core.Index{Name: indexName, Type: indexType, Cols: make([]string, 0)}
  1044. for _, colName := range colNames {
  1045. index.Cols = append(index.Cols, strings.Trim(colName, `" `))
  1046. }
  1047. index.IsRegular = isRegular
  1048. indexes[index.Name] = index
  1049. }
  1050. return indexes, nil
  1051. }
  1052. func (db *postgres) Filters() []core.Filter {
  1053. return []core.Filter{&core.IdFilter{}, &core.QuoteFilter{}, &core.SeqFilter{Prefix: "$", Start: 1}}
  1054. }
  1055. type pqDriver struct {
  1056. }
  1057. type values map[string]string
  1058. func (vs values) Set(k, v string) {
  1059. vs[k] = v
  1060. }
  1061. func (vs values) Get(k string) (v string) {
  1062. return vs[k]
  1063. }
  1064. func errorf(s string, args ...interface{}) {
  1065. panic(fmt.Errorf("pq: %s", fmt.Sprintf(s, args...)))
  1066. }
  1067. func parseURL(connstr string) (string, error) {
  1068. u, err := url.Parse(connstr)
  1069. if err != nil {
  1070. return "", err
  1071. }
  1072. if u.Scheme != "postgresql" && u.Scheme != "postgres" {
  1073. return "", fmt.Errorf("invalid connection protocol: %s", u.Scheme)
  1074. }
  1075. var kvs []string
  1076. escaper := strings.NewReplacer(` `, `\ `, `'`, `\'`, `\`, `\\`)
  1077. accrue := func(k, v string) {
  1078. if v != "" {
  1079. kvs = append(kvs, k+"="+escaper.Replace(v))
  1080. }
  1081. }
  1082. if u.User != nil {
  1083. v := u.User.Username()
  1084. accrue("user", v)
  1085. v, _ = u.User.Password()
  1086. accrue("password", v)
  1087. }
  1088. i := strings.Index(u.Host, ":")
  1089. if i < 0 {
  1090. accrue("host", u.Host)
  1091. } else {
  1092. accrue("host", u.Host[:i])
  1093. accrue("port", u.Host[i+1:])
  1094. }
  1095. if u.Path != "" {
  1096. accrue("dbname", u.Path[1:])
  1097. }
  1098. q := u.Query()
  1099. for k := range q {
  1100. accrue(k, q.Get(k))
  1101. }
  1102. sort.Strings(kvs) // Makes testing easier (not a performance concern)
  1103. return strings.Join(kvs, " "), nil
  1104. }
  1105. func parseOpts(name string, o values) {
  1106. if len(name) == 0 {
  1107. return
  1108. }
  1109. name = strings.TrimSpace(name)
  1110. ps := strings.Split(name, " ")
  1111. for _, p := range ps {
  1112. kv := strings.Split(p, "=")
  1113. if len(kv) < 2 {
  1114. errorf("invalid option: %q", p)
  1115. }
  1116. o.Set(kv[0], kv[1])
  1117. }
  1118. }
  1119. func (p *pqDriver) Parse(driverName, dataSourceName string) (*core.Uri, error) {
  1120. db := &core.Uri{DbType: core.POSTGRES}
  1121. o := make(values)
  1122. var err error
  1123. if strings.HasPrefix(dataSourceName, "postgresql://") || strings.HasPrefix(dataSourceName, "postgres://") {
  1124. dataSourceName, err = parseURL(dataSourceName)
  1125. if err != nil {
  1126. return nil, err
  1127. }
  1128. }
  1129. parseOpts(dataSourceName, o)
  1130. db.DbName = o.Get("dbname")
  1131. if db.DbName == "" {
  1132. return nil, errors.New("dbname is empty")
  1133. }
  1134. /*db.Schema = o.Get("schema")
  1135. if len(db.Schema) == 0 {
  1136. db.Schema = "public"
  1137. }*/
  1138. return db, nil
  1139. }