postgres_dialect.go 38 KB

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