text: (window.webpackJsonp=window.webpackJsonp||[]).push([[14],{167:function(t,s,a){"use strict";a.r(s);var n=a(0),e=Object(n.a)({},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"content"},[t._m(0),t._v(" "),t._m(1),t._v(" "),t._m(2),t._v(" "),t._m(3),t._v(" "),a("p",[t._v("Default Port: 5433 or 5432")]),t._v(" "),t._m(4),t._v(" "),a("p",[t._v("First, initialize directory:")]),t._v(" "),t._m(5),t._v(" "),a("p",[t._v("Then run to start:")]),t._v(" "),t._m(6),t._v(" "),a("p",[t._v("To stop :")]),t._v(" "),t._m(7),t._v(" "),a("p",[t._v("To restart:")]),t._v(" "),t._m(8),t._v(" "),t._m(9),t._v(" "),a("p",[t._v("http://initd.org/psycopg/docs/usage.html")]),t._v(" "),t._m(10),t._v(" "),t._m(11),t._v(" "),t._m(12),t._v(" "),a("p",[t._v("Append line to file /etc/postgresql/9.3/main/pg_hba.conf")]),t._v(" "),t._m(13),t._v(" "),a("p",[t._v("Append line to file /etc/postgresql/9.3/main/postgresql.conf")]),t._v(" "),t._m(14),t._v(" "),t._m(15),t._v(" "),t._m(16),t._m(17),t._v(" "),a("p",[t._v("sudo apt-get install postgresql-client")]),t._v(" "),t._m(18),t._v(" "),t._m(19),t._v(" "),t._m(20),t._m(21),t._v(" "),a("p",[t._v("In your postgresql.conf file, change the log_statement setting to 'all':")]),t._v(" "),t._m(22),t._m(23),t._v(" "),a("p",[t._v("http://pgfouine.projects.pgfoundry.org/")]),t._v(" "),t._m(24),t._v(" "),t._m(25),t._v(" "),t._m(26),t._v(" "),t._m(27),t._v(" "),a("p",[t._v("http://www.postgresql.org/docs/9.3/static/app-pgdump.html")]),t._v(" "),a("p",[t._v("Example:")]),t._v(" "),t._m(28),t._v(" "),t._m(29),t._v(" "),a("p",[t._v("http://www.postgresql.org/docs/9.3/static/app-pgrestore.html")]),t._v(" "),t._m(30),t._v(" "),t._m(31),t._v(" "),t._m(32),t._v(" "),a("p",[t._v("http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql")]),t._v(" "),t._m(33),t._m(34),t._v(" "),t._m(35),t._m(36),t._v(" "),t._m(37),t._v(" "),t._m(38),t._v(" "),t._m(39),t._v(" "),t._m(40),t._v(" "),t._m(41),t._v(" "),t._m(42),t._m(43),t._v(" "),t._m(44),t._m(45),t._v(" "),t._m(46),t._m(47),t._v(" "),t._m(48),t._m(49),t._v(" "),t._m(50),t._v(" "),t._m(51),t._v(" "),t._m(52),t._v(" "),a("p",[t._v("This can be done by creating a .pgpass file in the home directory of the (Linux) User. .pgpass file format:")]),t._v(" "),a("p",[t._v("To set up the file:")]),t._v(" "),t._m(53),t._m(54),t._v(" "),a("p",[t._v("http://www.postgresql.org/docs/9.5/static/sql-copy.html")]),t._v(" "),t._m(55),t._v(" "),t._m(56),t._v(" "),a("p",[t._v("By Aggregate")]),t._v(" "),t._m(57),t._m(58),t._v(" "),t._m(59),t._m(60),t._v(" "),t._m(61),t._m(62),t._v(" "),t._m(63),t._v(" "),a("p",[t._v("First value in group: "),a("a",{attrs:{href:"https://stackoverflow.com/a/25534279",target:"_blank",rel:"noopener noreferrer"}},[t._v("https://stackoverflow.com/a/25534279"),a("OutboundLink")],1)]),t._v(" "),t._m(64),t._m(65),t._v(" "),a("p",[t._v("http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/")]),t._v(" "),a("p",[t._v("http://www.postgresql.org/docs/9.3/static/functions-json.html")]),t._v(" "),t._m(66),t._v(" "),t._m(67),t._v(" "),t._m(68),t._m(69),t._v(" "),t._m(70),t._v(" "),t._m(71),t._m(72),t._v(" "),t._m(73),t._v(" "),t._m(74),t._v(" "),t._m(75),t._v(" "),t._m(76),t._v(" "),t._m(77),t._v(" "),t._m(78),t._v(" "),t._m(79),t._v(" "),t._m(80),t._v(" "),t._m(81),t._v(" "),a("p",[a("a",{attrs:{href:"https://gist.github.com/pepoviola/21c1cb00bac65a111568",target:"_blank",rel:"noopener noreferrer"}},[t._v("https://gist.github.com/pepoviola/21c1cb00bac65a111568"),a("OutboundLink")],1)]),t._v(" "),t._m(82),t._m(83),t._v(" "),a("p",[a("a",{attrs:{href:"https://www.postgresql.org/docs/current/libpq-ssl.html",target:"_blank",rel:"noopener noreferrer"}},[t._v("https://www.postgresql.org/docs/current/libpq-ssl.html"),a("OutboundLink")],1)]),t._v(" "),t._m(84)])},[function(){var t=this.$createElement,s=this._self._c||t;return s("h1",{attrs:{id:"postgesql"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#postgesql","aria-hidden":"true"}},[this._v("#")]),this._v(" PostgeSQL")])},function(){var t=this.$createElement,s=this._self._c||t;return s("h2",{attrs:{id:"configuration"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#configuration","aria-hidden":"true"}},[this._v("#")]),this._v(" Configuration")])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"installation"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#installation","aria-hidden":"true"}},[this._v("#")]),this._v(" Installation")])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("apt-get install postgresql postgresql-client")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"manual-installation"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#manual-installation","aria-hidden":"true"}},[this._v("#")]),this._v(" Manual Installation")])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("./initdb /dbdir")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("./pg_ctl -D /dbdir -l logfile start")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("./pg_ctl -D /dbdir -l logfile stop")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("./pg_ctl -D /dbdir -l logfile restart")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"python"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#python","aria-hidden":"true"}},[this._v("#")]),this._v(" Python")])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"config-file"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#config-file","aria-hidden":"true"}},[this._v("#")]),this._v(" Config File")])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("/etc/postgresql/9.3/main/postgresql.conf")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h4",{attrs:{id:"allow-all-remote-connection"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#allow-all-remote-connection","aria-hidden":"true"}},[this._v("#")]),this._v(" Allow all remote connection")])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("host all all 0.0.0.0/0 trust")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("listen_addresses = '*'")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h4",{attrs:{id:"start-stop"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#start-stop","aria-hidden":"true"}},[this._v("#")]),this._v(" Start/Stop")])},function(){var t=this.$createElement,s=this._self._c||t;return s("div",{staticClass:"language- extra-class"},[s("pre",{pre:!0,attrs:{class:"language-text"}},[s("code",[this._v("/etc/init.d/postgresql start\n\n/etc/init.d/postgresql restart\n\n/etc/init.d/postgresql stop\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h4",{attrs:{id:"install-client"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#install-client","aria-hidden":"true"}},[this._v("#")]),this._v(" Install Client")])},function(){var t=this.$createElement,s=this._self._c||t;return s("h4",{attrs:{id:"query-logging"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#query-logging","aria-hidden":"true"}},[this._v("#")]),this._v(" Query Logging")])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("strong",[this._v("User Session:")])])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" set_config"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token string"}},[t._v("'log_statement'")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v("'all'")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("true")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("strong",[this._v("Long term:")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("div",{staticClass:"language- extra-class"},[s("pre",{pre:!0,attrs:{class:"language-text"}},[s("code",[this._v("log_line_prefix = '%t %c %u ' # time sessionid user\n\nlog_directory = 'pg_log'\n\nlog_filename = 'postgresql-queries.log'\n\nlog_statement = 'all'\n\nlogging_collector = on\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("strong",[this._v("Log Analyzer")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h2",{attrs:{id:"command-line"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#command-line","aria-hidden":"true"}},[this._v("#")]),this._v(" Command Line")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("table",[a("thead",[a("tr",[a("th",[t._v("COMMAND")]),t._v(" "),a("th",[t._v("DESCRIPTION")])])]),t._v(" "),a("tbody",[a("tr",[a("td",[t._v("?")]),t._v(" "),a("td",[t._v("Help")])]),t._v(" "),a("tr",[a("td",[t._v("\\q")]),t._v(" "),a("td",[t._v("Quit/Exit")])]),t._v(" "),a("tr",[a("td",[t._v("\\c [db]")]),t._v(" "),a("td",[t._v("Connect to a database")])]),t._v(" "),a("tr",[a("td",[t._v("\\d [table]")]),t._v(" "),a("td",[t._v("Describe \\ Show table definition including triggers")])]),t._v(" "),a("tr",[a("td",[t._v("\\dt .")]),t._v(" "),a("td",[t._v("List tables from all schemas (if . is omitted will only show SEARCH_PATH ones)")])]),t._v(" "),a("tr",[a("td",[t._v("\\l")]),t._v(" "),a("td",[t._v("List databases")])]),t._v(" "),a("tr",[a("td",[t._v("\\i")]),t._v(" "),a("td",[t._v("executes the sql commands within the sql file. Example: \\i tournament.sql")])]),t._v(" "),a("tr",[a("td",[t._v("\\dn")]),t._v(" "),a("td",[t._v("List schemas")])]),t._v(" "),a("tr",[a("td",[t._v("\\df")]),t._v(" "),a("td",[t._v("List functions")])]),t._v(" "),a("tr",[a("td",[t._v("\\dv")]),t._v(" "),a("td",[t._v("List views")])]),t._v(" "),a("tr",[a("td",[t._v("\\df+ [function]")]),t._v(" "),a("td",[t._v("Show function SQL code. \\x before pretty-formats it")])])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h2",{attrs:{id:"backup"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#backup","aria-hidden":"true"}},[this._v("#")]),this._v(" Backup")])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"pg-dump"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#pg-dump","aria-hidden":"true"}},[this._v("#")]),this._v(" pg_dump")])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v('pg_dump --host=ibmhpgdb1.dhcp.internetbrands.com --port=5432 --dbname=mh --username=ll4_reader --table=ll4_1.persona --data-only > "C:_\\WORK\\Freelance\\UpWork\\rrsoft\\Data\\ll4_1\\persona.SQL"')])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"pg-restore"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#pg-restore","aria-hidden":"true"}},[this._v("#")]),this._v(" pg_restore")])},function(){var t=this.$createElement,s=this._self._c||t;return s("h2",{attrs:{id:"sql-queries"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#sql-queries","aria-hidden":"true"}},[this._v("#")]),this._v(" SQL Queries")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("table",[a("thead",[a("tr",[a("th",[t._v("QUERY")]),t._v(" "),a("th",[t._v("DESCRIPTION")])])]),t._v(" "),a("tbody",[a("tr",[a("td",[t._v("SELECT * FROM pg_proc WHERE proname='procedurename'")]),t._v(" "),a("td",[t._v("List procedure/function")])]),t._v(" "),a("tr",[a("td",[t._v("SELECT * FROM pg_views WHERE viewname='viewname';")]),t._v(" "),a("td",[t._v("List view (including the definition)")])]),t._v(" "),a("tr",[a("td",[t._v("SELECT pg_size_pretty(pg_total_relation_size('table_name'));")]),t._v(" "),a("td",[t._v("Show DB table space in use")])]),t._v(" "),a("tr",[a("td",[t._v("SELECT pg_size_pretty(pg_database_size('database_name'));")]),t._v(" "),a("td",[t._v("Show DB space in use")])]),t._v(" "),a("tr",[a("td",[t._v("show statement_timeout;")]),t._v(" "),a("td",[t._v("Show current user's statement timeout")])]),t._v(" "),a("tr",[a("td",[t._v("SELECT pid, datname, waiting, state, query FROM pg_stat_activity WHERE datname='database_name';")]),t._v(" "),a("td",[t._v("Show queries being executed at a certain DB. Can also display query time, etc.")])]),t._v(" "),a("tr",[a("td",[t._v("SELECT * FROM pg_indexes WHERE tablename='table_name' AND schemaname='schema_name';")]),t._v(" "),a("td",[t._v("Show table indexes")])])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"upsert"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#upsert","aria-hidden":"true"}},[this._v("#")]),this._v(" UPSERT")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{attrs:{class:"token keyword"}},[t._v("UPDATE")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("table")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("SET")]),t._v(" field"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),a("span",{attrs:{class:"token string"}},[t._v("'C'")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" field2"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),a("span",{attrs:{class:"token string"}},[t._v("'Z'")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" id"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),a("span",{attrs:{class:"token number"}},[t._v("3")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("INSERT")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("INTO")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("table")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" field"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" field2"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("3")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v("'C'")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v("'Z'")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("EXISTS")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("table")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" id"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),a("span",{attrs:{class:"token number"}},[t._v("3")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"table-lock"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#table-lock","aria-hidden":"true"}},[this._v("#")]),this._v(" Table Lock")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("select")]),t._v(" pid"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" \n       usename"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" \n       pg_blocking_pids"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("pid"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("as")]),t._v(" blocked_by"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" \n       query "),a("span",{attrs:{class:"token keyword"}},[t._v("as")]),t._v(" blocked_query\n"),a("span",{attrs:{class:"token keyword"}},[t._v("from")]),t._v(" pg_stat_activity\n"),a("span",{attrs:{class:"token keyword"}},[t._v("where")]),t._v(" cardinality"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("pg_blocking_pids"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("pid"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v(">")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("0")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" pg_stat_activity "),a("span",{attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" state "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v("'active'")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" pg_cancel_backend"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token number"}},[t._v("32143")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"information-schema"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#information-schema","aria-hidden":"true"}},[this._v("#")]),this._v(" Information Schema")])},function(){var t=this.$createElement,s=this._self._c||t;return s("h4",{attrs:{id:"all-columns"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#all-columns","aria-hidden":"true"}},[this._v("#")]),this._v(" All columns")])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("SELECT * FROM information_schema.columns;")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h4",{attrs:{id:"all-tables"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#all-tables","aria-hidden":"true"}},[this._v("#")]),this._v(" All Tables")])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("SELECT * FROM information_schema.tables;")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h4",{attrs:{id:"all-indexes"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#all-indexes","aria-hidden":"true"}},[this._v("#")]),this._v(" All indexes")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v("\nU"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("usename "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" user_name"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nns"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("nspname "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" schema_name"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nidx"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("indrelid :: REGCLASS "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" table_name"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\ni"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("relname               "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" index_name"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nidx"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("indisunique "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" is_unique"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nidx"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("indisprimary "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" is_primary"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nam"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("amname "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" index_type"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nidx"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("indkey"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nARRAY"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" pg_get_indexdef"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("idx"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("indexrelid"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" k "),a("span",{attrs:{class:"token operator"}},[t._v("+")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("TRUE")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("\n  generate_subscripts"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("idx"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("indkey"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" k\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" k\n"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" index_keys"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("idx"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("indexprs "),a("span",{attrs:{class:"token operator"}},[t._v("IS")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("NULL")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("OR")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("idx"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("indkey::"),a("span",{attrs:{class:"token keyword"}},[t._v("int")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v(" @"),a("span",{attrs:{class:"token operator"}},[t._v(">")]),t._v(" array"),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token number"}},[t._v("0")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" is_functional"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nidx"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("indpred "),a("span",{attrs:{class:"token operator"}},[t._v("IS")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("NULL")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" is_partial\n"),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" pg_index "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" idx\n"),a("span",{attrs:{class:"token keyword"}},[t._v("JOIN")]),t._v(" pg_class "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" i\n"),a("span",{attrs:{class:"token keyword"}},[t._v("ON")]),t._v(" i"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("oid "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" idx"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("indexrelid\n"),a("span",{attrs:{class:"token keyword"}},[t._v("JOIN")]),t._v(" pg_am "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" am\n"),a("span",{attrs:{class:"token keyword"}},[t._v("ON")]),t._v(" i"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("relam "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" am"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("oid\n"),a("span",{attrs:{class:"token keyword"}},[t._v("JOIN")]),t._v(" pg_namespace "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" NS "),a("span",{attrs:{class:"token keyword"}},[t._v("ON")]),t._v(" i"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("relnamespace "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" NS"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("OID\n"),a("span",{attrs:{class:"token keyword"}},[t._v("JOIN")]),t._v(" pg_user "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" U "),a("span",{attrs:{class:"token keyword"}},[t._v("ON")]),t._v(" i"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("relowner "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" U"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("usesysid\n"),a("span",{attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" nspname "),a("span",{attrs:{class:"token operator"}},[t._v("LIKE")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v("'pg%'")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v(" "),a("span",{attrs:{class:"token comment"}},[t._v("-- Excluding system tables;")]),t._v("\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h4",{attrs:{id:"all-primary-keys"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#all-primary-keys","aria-hidden":"true"}},[this._v("#")]),this._v(" All Primary Keys")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{attrs:{class:"token keyword"}},[t._v("select")]),t._v("\ntc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_schema"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_name"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" kc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("column_name"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("constraint_type\n"),a("span",{attrs:{class:"token keyword"}},[t._v("from")]),t._v("\ninformation_schema"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_constraints tc\n"),a("span",{attrs:{class:"token keyword"}},[t._v("join")]),t._v(" information_schema"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("key_column_usage kc "),a("span",{attrs:{class:"token keyword"}},[t._v("on")]),t._v("\nkc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_name "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_name\n"),a("span",{attrs:{class:"token operator"}},[t._v("and")]),t._v(" kc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("constraint_name "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("constraint_name\n"),a("span",{attrs:{class:"token keyword"}},[t._v("where")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token operator"}},[t._v("=")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),t._v("\n"),a("span",{attrs:{class:"token comment"}},[t._v("-- and tc.table_schema = 'account_account'")]),t._v("\n"),a("span",{attrs:{class:"token comment"}},[t._v("-- and tc.table_name = 'account_account'")]),t._v("\n"),a("span",{attrs:{class:"token operator"}},[t._v("and")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("constraint_type "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v("'PRIMARY KEY'")]),t._v("\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h4",{attrs:{id:"all-foreign-keys"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#all-foreign-keys","aria-hidden":"true"}},[this._v("#")]),this._v(" All Foreign Keys")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{attrs:{class:"token keyword"}},[t._v("select")]),t._v("\ntc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_schema"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_name"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" kc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("column_name"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("constraint_type\n"),a("span",{attrs:{class:"token keyword"}},[t._v("from")]),t._v("\ninformation_schema"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_constraints tc\n"),a("span",{attrs:{class:"token keyword"}},[t._v("join")]),t._v(" information_schema"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("key_column_usage kc "),a("span",{attrs:{class:"token keyword"}},[t._v("on")]),t._v("\nkc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_name "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_name\n"),a("span",{attrs:{class:"token operator"}},[t._v("and")]),t._v(" kc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("constraint_name "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("constraint_name\n"),a("span",{attrs:{class:"token keyword"}},[t._v("where")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token operator"}},[t._v("=")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),t._v("\n"),a("span",{attrs:{class:"token comment"}},[t._v("-- and tc.table_schema = 'account_account'")]),t._v("\n"),a("span",{attrs:{class:"token comment"}},[t._v("-- and tc.table_name = 'account_account'")]),t._v("\n"),a("span",{attrs:{class:"token operator"}},[t._v("and")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("constraint_type "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v("'FOREIGN KEY'")]),t._v("\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h4",{attrs:{id:"all-constraints"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#all-constraints","aria-hidden":"true"}},[this._v("#")]),this._v(" All Constraints")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{attrs:{class:"token keyword"}},[t._v("select")]),t._v("\ntc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_schema"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_name"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" kc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("column_name"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("constraint_type\n"),a("span",{attrs:{class:"token keyword"}},[t._v("from")]),t._v("\ninformation_schema"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_constraints tc\n"),a("span",{attrs:{class:"token keyword"}},[t._v("join")]),t._v(" information_schema"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("key_column_usage kc "),a("span",{attrs:{class:"token keyword"}},[t._v("on")]),t._v("\nkc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_name "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("table_name\n"),a("span",{attrs:{class:"token operator"}},[t._v("and")]),t._v(" kc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("constraint_name "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" tc"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("constraint_name\n"),a("span",{attrs:{class:"token keyword"}},[t._v("where")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token operator"}},[t._v("=")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),t._v("\n"),a("span",{attrs:{class:"token comment"}},[t._v("-- and tc.table_schema = 'account_account'")]),t._v("\n"),a("span",{attrs:{class:"token comment"}},[t._v("-- and tc.table_name = 'account_account'")]),t._v("\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"data-conversion"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#data-conversion","aria-hidden":"true"}},[this._v("#")]),this._v(" Data Conversion")])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("strong",[this._v("From String to Number")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("CAST(nullif(data->>'retweet_count', '') AS decimal)")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"using-command-line"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#using-command-line","aria-hidden":"true"}},[this._v("#")]),this._v(" Using Command line")])},function(){var t=this.$createElement,s=this._self._c||t;return s("div",{staticClass:"language- extra-class"},[s("pre",{pre:!0,attrs:{class:"language-text"}},[s("code",[this._v('echo "192.168.1.1:::postgres:postgrespwd" > $HOME/.pgpass\n\necho "chmod 0600 $HOME/.pgpass"\n\necho " psql -h 192.168.1.1 -p 5432<span style="mso-spacerun:yes"></span> -U postgres<span style="mso-spacerun:yes"></span> postgres<span style="mso-spacerun:yes"></span> -f tmp.sql\n\npsql –U admin –p myPass -d db1 -c \'SELECT * FROM db1.itevia_twitter\'\n\n')])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h2",{attrs:{id:"csv-import-export"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#csv-import-export","aria-hidden":"true"}},[this._v("#")]),this._v(" CSV Import / Export")])},function(){var t=this.$createElement,s=this._self._c||t;return s("h2",{attrs:{id:"functions"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#functions","aria-hidden":"true"}},[this._v("#")]),this._v(" FUNCTIONS")])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"median"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#median","aria-hidden":"true"}},[this._v("#")]),this._v(" Median")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{attrs:{class:"token keyword"}},[t._v("CREATE")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("OR")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("REPLACE")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("FUNCTION")]),t._v(" array_median"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token keyword"}},[t._v("numeric")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("RETURNS")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("numeric")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v("\n\n$\n"),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("CASE")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("WHEN")]),t._v(" array_upper"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("$"),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("0")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("THEN")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("null")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("ELSE")]),t._v(" asorted"),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),t._v("ceiling"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("array_upper"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("asorted"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token operator"}},[t._v("/")]),a("span",{attrs:{class:"token number"}},[t._v("2.0")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("END")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" ARRAY"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("$"),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),t._v("n"),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("\ngenerate_series"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" array_upper"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("$"),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("$"),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),t._v("n"),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("IS")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("NULL")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("$"),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),t._v("n"),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v("\n"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("As")]),t._v(" asorted"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("As")]),t._v(" foo "),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n$\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("LANGUAGE")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v("'sql'")]),t._v(" IMMUTABLE"),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n \n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("CREATE")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("OR")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("REPLACE")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("FUNCTION")]),t._v(" array_median"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token keyword"}},[t._v("timestamp")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("RETURNS")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("timestamp")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v("\n$\n"),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("CASE")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("WHEN")]),t._v(" array_upper"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("$"),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("0")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("THEN")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("null")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("ELSE")]),t._v(" asorted"),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),t._v("ceiling"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("array_upper"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("asorted"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token operator"}},[t._v("/")]),a("span",{attrs:{class:"token number"}},[t._v("2.0")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("END")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" ARRAY"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("$"),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),t._v("n"),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("\ngenerate_series"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" array_upper"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("$"),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("$"),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),t._v("n"),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("IS")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("NULL")]),t._v("\n    "),a("span",{attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("$"),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),t._v("n"),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v("\n"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("As")]),t._v(" asorted"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("As")]),t._v(" foo "),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n$\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("LANGUAGE")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v("'sql'")]),t._v(" IMMUTABLE"),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("CREATE")]),t._v(" AGGREGATE median"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token keyword"}},[t._v("numeric")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\nSFUNC"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v("array_append"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nSTYPE"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),a("span",{attrs:{class:"token keyword"}},[t._v("numeric")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nFINALFUNC"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v("array_median\n"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n \n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("CREATE")]),t._v(" AGGREGATE median"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token keyword"}},[t._v("timestamp")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\nSFUNC"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v("array_append"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nSTYPE"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),a("span",{attrs:{class:"token keyword"}},[t._v("timestamp")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nFINALFUNC"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v("array_median\n"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n \n\n"),a("span",{attrs:{class:"token comment"}},[t._v("-- To Test")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token function"}},[t._v("avg")]),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("x"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" median"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("x"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("3")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("As")]),t._v(" x\n"),a("span",{attrs:{class:"token keyword"}},[t._v("UNION")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("ALL")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("-")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("As")]),t._v(" x\n"),a("span",{attrs:{class:"token keyword"}},[t._v("UNION")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("ALL")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("11")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("As")]),t._v(" x\n"),a("span",{attrs:{class:"token keyword"}},[t._v("UNION")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("ALL")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("10")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("As")]),t._v(" x\n"),a("span",{attrs:{class:"token keyword"}},[t._v("UNION")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("ALL")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("9")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("As")]),t._v(" x"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("As")]),t._v(" foo"),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("By")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("Function")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("CREATE")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("FUNCTION")]),t._v(" _final_median"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("anyarray"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("RETURNS")]),t._v(" float8 "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" $\n"),a("span",{attrs:{class:"token keyword"}},[t._v("WITH")]),t._v(" q "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v("\n"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" val\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" unnest"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("$"),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" val\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" VAL "),a("span",{attrs:{class:"token operator"}},[t._v("IS")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("NULL")]),t._v("\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1")]),t._v("\n"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n\ncnt "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v("\n"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token function"}},[t._v("COUNT")]),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token operator"}},[t._v("*")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" c "),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" q\n"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{attrs:{class:"token function"}},[t._v("AVG")]),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("val"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v("::float8\n"),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("\n"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" val "),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" q\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("LIMIT")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("2")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("-")]),t._v(" "),a("span",{attrs:{class:"token function"}},[t._v("MOD")]),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" c "),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" cnt"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("2")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("OFFSET")]),t._v(" GREATEST"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("CEIL"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" c "),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" cnt"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("/")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("2.0")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("-")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),a("span",{attrs:{class:"token number"}},[t._v("0")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" q2"),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n$ "),a("span",{attrs:{class:"token keyword"}},[t._v("LANGUAGE")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("sql")]),t._v(" IMMUTABLE"),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("CREATE")]),t._v(" AGGREGATE median"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("anyelement"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\nSFUNC"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v("array_append"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nSTYPE"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v("anyarray"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nFINALFUNC"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v("_final_median"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nINITCOND"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),a("span",{attrs:{class:"token string"}},[t._v("'{}'")]),t._v("\n"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h4",{attrs:{id:"example"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#example","aria-hidden":"true"}},[this._v("#")]),this._v(" Example")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{attrs:{class:"token keyword"}},[t._v("CREATE")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("OR")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("REPLACE")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("FUNCTION")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v('"cs"')]),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),a("span",{attrs:{class:"token string"}},[t._v('"add_event"')]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_event_type "),a("span",{attrs:{class:"token keyword"}},[t._v("character")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("varying")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_trigger_url_host "),a("span",{attrs:{class:"token keyword"}},[t._v("character")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("varying")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_trigger_url_domain "),a("span",{attrs:{class:"token keyword"}},[t._v("character")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("varying")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_trigger_url_path "),a("span",{attrs:{class:"token keyword"}},[t._v("character")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("varying")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_trigger_url_query "),a("span",{attrs:{class:"token keyword"}},[t._v("character")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("varying")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_referrer_url_host "),a("span",{attrs:{class:"token keyword"}},[t._v("character")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("varying")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_referrer_url_domain "),a("span",{attrs:{class:"token keyword"}},[t._v("character")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("varying")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_referrer_url_path "),a("span",{attrs:{class:"token keyword"}},[t._v("character")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("varying")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_referrer_url_query "),a("span",{attrs:{class:"token keyword"}},[t._v("character")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("varying")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_user_agent "),a("span",{attrs:{class:"token keyword"}},[t._v("character")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("varying")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_client_ip "),a("span",{attrs:{class:"token keyword"}},[t._v("character")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("varying")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_session_id "),a("span",{attrs:{class:"token keyword"}},[t._v("numeric")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_local_user_id "),a("span",{attrs:{class:"token keyword"}},[t._v("numeric")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_global_user_id "),a("span",{attrs:{class:"token keyword"}},[t._v("numeric")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_time_stamp "),a("span",{attrs:{class:"token keyword"}},[t._v("numeric")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" p_event_params "),a("span",{attrs:{class:"token keyword"}},[t._v("character")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("varying")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("RETURNS")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),t._v("\n\nVOLATILE\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" $dbvis$\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("declare")]),t._v("\n\nretval_id "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_event_type_id "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_trigger_url_host_id "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_trigger_url_domain_id "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_trigger_url_path_id "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_trigger_url_query_id "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_referrer_url_host_id "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_referrer_url_domain_id "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_referrer_url_path_id "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_referrer_url_query_id "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_user_agent_id "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_client_ip_id "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_input_params_size "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_event_param_id_array "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_param "),a("span",{attrs:{class:"token keyword"}},[t._v("character")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("varying")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\ndim1 "),a("span",{attrs:{class:"token keyword"}},[t._v("int")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\ndim2 "),a("span",{attrs:{class:"token keyword"}},[t._v("int")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("begin")]),t._v("\n\nv_event_type_id "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("get_event_type_id_from_name"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_event_type"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_trigger_url_host_id "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("get_host_id_from_name"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_trigger_url_host"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_trigger_url_domain_id "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("get_domain_id_from_name"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_trigger_url_domain"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_trigger_url_path_id "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("get_path_id_from_name"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_trigger_url_query"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_trigger_url_query_id "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("get_query_string_id_from_name"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_trigger_url_query"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("if")]),t._v(" p_referrer_url_host "),a("span",{attrs:{class:"token operator"}},[t._v("IS")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("NULL")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("then")]),t._v("\n\nv_referrer_url_host_id "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("get_host_id_from_name"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_referrer_url_host"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("end")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("if")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("if")]),t._v(" p_referrer_url_domain "),a("span",{attrs:{class:"token operator"}},[t._v("IS")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("NULL")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("then")]),t._v("\n\nv_referrer_url_domain_id "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("get_domain_id_from_name"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_referrer_url_domain"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("end")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("if")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("if")]),t._v(" p_referrer_url_path "),a("span",{attrs:{class:"token operator"}},[t._v("IS")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("NULL")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("then")]),t._v("\n\nv_referrer_url_path_id "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("get_path_id_from_name"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_referrer_url_query"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("end")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("if")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("if")]),t._v(" p_referrer_url_query "),a("span",{attrs:{class:"token operator"}},[t._v("IS")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("NOT")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("NULL")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("then")]),t._v("\n\nv_referrer_url_query_id "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("get_query_string_id_from_name"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_referrer_url_query"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("end")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("if")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n       \n\nv_user_agent_id "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("get_user_agent_id_from_name"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_user_agent"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_client_ip_id "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("get_client_ip_id_from_name"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_client_ip"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n       \n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("select")]),t._v(" array_length"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_event_params"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("into")]),t._v(" v_input_params_size"),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("if")]),t._v(" v_input_params_size "),a("span",{attrs:{class:"token operator"}},[t._v("is")]),t._v(" "),a("span",{attrs:{class:"token boolean"}},[t._v("null")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("then")]),t._v("\n\nv_input_params_size :"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("0")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("end")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("if")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("FOR")]),t._v(" i "),a("span",{attrs:{class:"token operator"}},[t._v("in")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1.")]),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("v_input_params_size "),a("span",{attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("2")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("LOOP")]),t._v("\n\nv_event_param_id_array"),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),t._v("i"),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v(" :"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("get_attribute_name_id_from_name"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_event_params"),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),t._v("i"),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nv_event_param_id_array"),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),t._v("i"),a("span",{attrs:{class:"token operator"}},[t._v("+")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v(" :"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("get_attribute_value_id_from_value"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("p_event_params"),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),t._v("i"),a("span",{attrs:{class:"token operator"}},[t._v("+")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("END")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("LOOP")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n       \n\nretval_id "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" cs"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("add_normalized_event\n\n"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n  v_event_type_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  v_trigger_url_host_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  v_trigger_url_domain_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  v_trigger_url_path_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  v_trigger_url_query_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  v_referrer_url_host_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  v_referrer_url_domain_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  v_referrer_url_path_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  v_referrer_url_query_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  v_user_agent_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  v_client_ip_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  p_session_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  p_local_user_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  p_global_user_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  p_time_stamp"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  v_event_param_id_array\n"),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n \n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("return")]),t._v(" retval_id"),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\nexception\n \n\n"),a("span",{attrs:{class:"token keyword"}},[t._v("when")]),t._v(" plpgsql_error "),a("span",{attrs:{class:"token keyword"}},[t._v("then")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("return")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("-")]),a("span",{attrs:{class:"token number"}},[t._v("2")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("when")]),t._v(" unique_violation "),a("span",{attrs:{class:"token keyword"}},[t._v("then")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("return")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("-")]),a("span",{attrs:{class:"token number"}},[t._v("3")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("end")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n$dbvis$ "),a("span",{attrs:{class:"token keyword"}},[t._v("LANGUAGE")]),t._v(" plpgsql\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"return-array"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#return-array","aria-hidden":"true"}},[this._v("#")]),this._v(" Return Array")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{attrs:{class:"token keyword"}},[t._v("DROP")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("FUNCTION")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("IF")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("EXISTS")]),t._v(" get_report_event_type_ids2"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("report_name "),a("span",{attrs:{class:"token keyword"}},[t._v("VARCHAR")]),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token number"}},[t._v("100")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("CREATE")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v("OR")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("REPLACE")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("FUNCTION")]),t._v(" get_report_event_type_ids2"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("report_name "),a("span",{attrs:{class:"token keyword"}},[t._v("varchar")]),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token number"}},[t._v("100")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("RETURNS")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v("\n$BODY$\n"),a("span",{attrs:{class:"token keyword"}},[t._v("DECLARE")]),t._v("\nb "),a("span",{attrs:{class:"token keyword"}},[t._v("integer")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("BEGIN")]),t._v("\nb "),a("span",{attrs:{class:"token operator"}},[t._v("=")]),t._v(" ARRAY"),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),a("span",{attrs:{class:"token number"}},[t._v("2")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),a("span",{attrs:{class:"token number"}},[t._v("56")]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),a("span",{attrs:{class:"token number"}},[t._v("3141")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("RETURN")]),t._v(" b"),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("END")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n$BODY$ "),a("span",{attrs:{class:"token keyword"}},[t._v("LANGUAGE")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v("'plpgsql'")]),t._v(" VOLATILE"),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h2",{attrs:{id:"windowing"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#windowing","aria-hidden":"true"}},[this._v("#")]),this._v(" Windowing")])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"rank-in-group-by"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#rank-in-group-by","aria-hidden":"true"}},[this._v("#")]),this._v(" Rank in Group By")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{attrs:{class:"token keyword"}},[t._v("select")]),t._v("\n  rown"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  description"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("array_agg"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("rul_limit "),a("span",{attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" regex_rk "),a("span",{attrs:{class:"token keyword"}},[t._v("ASC")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("as")]),t._v(" regex_rk"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  "),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("array_agg"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),t._v("product "),a("span",{attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" regex_rk "),a("span",{attrs:{class:"token keyword"}},[t._v("ASC")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{attrs:{class:"token punctuation"}},[t._v("[")]),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v("]")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("as")]),t._v(" product\n"),a("span",{attrs:{class:"token keyword"}},[t._v("from")]),t._v(" matched_final\n"),a("span",{attrs:{class:"token keyword"}},[t._v("group")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("by")]),t._v("\n  rown"),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  description\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h2",{attrs:{id:"json"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#json","aria-hidden":"true"}},[this._v("#")]),this._v(" JSON")])},function(){var t=this.$createElement,s=this._self._c||t;return s("h2",{attrs:{id:"access-security"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#access-security","aria-hidden":"true"}},[this._v("#")]),this._v(" Access & Security")])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"initial-access"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#initial-access","aria-hidden":"true"}},[this._v("#")]),this._v(" Initial Access")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-bash extra-class"},[a("pre",{pre:!0,attrs:{class:"language-bash"}},[a("code",[a("span",{attrs:{class:"token comment"}},[t._v("# su - postgres")]),t._v("\n\n$ psql\n\npostgres"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),a("span",{attrs:{class:"token comment"}},[t._v("# CREATE USER mypguser WITH PASSWORD 'mypguserpass';")]),t._v("\n\npostgres"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),a("span",{attrs:{class:"token comment"}},[t._v("# CREATE DATABASE mypgdatabase OWNER mypguser;")]),t._v("\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h3",{attrs:{id:"users"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#users","aria-hidden":"true"}},[this._v("#")]),this._v(" Users")])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("strong",[this._v("Show all Users:")])])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v("\n  u"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("usename "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v('"User_name"')]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  u"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("usesysid "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v('"User_ID"')]),a("span",{attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("CASE")]),t._v("\n    "),a("span",{attrs:{class:"token keyword"}},[t._v("WHEN")]),t._v(" u"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("usesuper "),a("span",{attrs:{class:"token operator"}},[t._v("AND")]),t._v(" u"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("usecreatedb "),a("span",{attrs:{class:"token keyword"}},[t._v("THEN")]),t._v(" CAST"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token string"}},[t._v("'superuser, create database'")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" pg_catalog"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),a("span",{attrs:{class:"token keyword"}},[t._v("text")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n    "),a("span",{attrs:{class:"token keyword"}},[t._v("WHEN")]),t._v(" u"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("usesuper "),a("span",{attrs:{class:"token keyword"}},[t._v("THEN")]),t._v(" CAST"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token string"}},[t._v("'superuser'")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" pg_catalog"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),a("span",{attrs:{class:"token keyword"}},[t._v("text")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n    "),a("span",{attrs:{class:"token keyword"}},[t._v("WHEN")]),t._v(" u"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("usecreatedb "),a("span",{attrs:{class:"token keyword"}},[t._v("THEN")]),t._v(" CAST"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token string"}},[t._v("'create database'")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" pg_catalog"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),a("span",{attrs:{class:"token keyword"}},[t._v("text")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n    "),a("span",{attrs:{class:"token keyword"}},[t._v("ELSE")]),t._v(" CAST"),a("span",{attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{attrs:{class:"token string"}},[t._v("''")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" pg_catalog"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),a("span",{attrs:{class:"token keyword"}},[t._v("text")]),a("span",{attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n  "),a("span",{attrs:{class:"token keyword"}},[t._v("END")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v('"Attributes"')]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" pg_catalog"),a("span",{attrs:{class:"token punctuation"}},[t._v(".")]),t._v("pg_user u\n"),a("span",{attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),a("span",{attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" "),a("span",{attrs:{class:"token number"}},[t._v("1")]),a("span",{attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("strong",[this._v("Create User:")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("CREATE USER mypguser WITH PASSWORD 'mypguserpass';")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("strong",[this._v("Make user SUPERUSER:")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("ALTER USER admin WITH SUPERUSER;")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("strong",[this._v("Making User a database owner:")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("CREATE DATABASE db1 OWNER admin;")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("strong",[this._v("Change user Password")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("p",[s("code",[this._v("ALTER USER postgres with password 'new-password';")])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h1",{attrs:{id:"ssl"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#ssl","aria-hidden":"true"}},[this._v("#")]),this._v(" SSL")])},function(){var t=this.$createElement,s=this._self._c||t;return s("h2",{attrs:{id:"generate-config"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#generate-config","aria-hidden":"true"}},[this._v("#")]),this._v(" Generate & Config")])},function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("div",{staticClass:"language-bash extra-class"},[a("pre",{pre:!0,attrs:{class:"language-bash"}},[a("code",[a("span",{attrs:{class:"token function"}},[t._v("mkdir")]),t._v(" cert\n"),a("span",{attrs:{class:"token function"}},[t._v("cd")]),t._v(" cert\n\n"),a("span",{attrs:{class:"token comment"}},[t._v("#### Generate #####")]),t._v("\n"),a("span",{attrs:{class:"token comment"}},[t._v("# Generate a private key (you must provide a passphrase).")]),t._v("\nopenssl genrsa -des3 -out server.key 1024\n\n"),a("span",{attrs:{class:"token comment"}},[t._v("# Remove the passphrase.")]),t._v("\nopenssl rsa -in server.key -out server.key\n\n"),a("span",{attrs:{class:"token comment"}},[t._v("# Create server certificate")]),t._v("\nopenssl req -new -key server.key -days 3650 -out server.crt -x509 -subj "),a("span",{attrs:{class:"token string"}},[t._v("'/C=CA/ST=British Columbia/L=Comox/O=TheBrain.ca/CN=thebrain.ca/emailAddress=info@thebrain.ca'")]),t._v("\n"),a("span",{attrs:{class:"token function"}},[t._v("cp")]),t._v(" server.crt root.crt\n\n"),a("span",{attrs:{class:"token comment"}},[t._v("# Find conf location")]),t._v("\n$ psql -U postgres\npostgres"),a("span",{attrs:{class:"token operator"}},[t._v("=")]),a("span",{attrs:{class:"token comment"}},[t._v("# SHOW config_file;")]),t._v("\n\n\n"),a("span",{attrs:{class:"token comment"}},[t._v("# Append to postgreql.conf")]),t._v("\n"),a("span",{attrs:{class:"token keyword"}},[t._v("echo")]),t._v(" "),a("span",{attrs:{class:"token string"}},[t._v("'\nssl = on\nssl_ciphers = '")]),t._v("DEFAULT:"),a("span",{attrs:{class:"token operator"}},[t._v("!")]),t._v("LOW:"),a("span",{attrs:{class:"token operator"}},[t._v("!")]),t._v("EXP:"),a("span",{attrs:{class:"token operator"}},[t._v("!")]),t._v("MD5:@STRENGTH"),a("span",{attrs:{class:"token string"}},[t._v("'\nssl_cert_file = '")]),t._v("/var/ssl/server.crt"),a("span",{attrs:{class:"token string"}},[t._v("'\nssl_key_file = '")]),t._v("/var/ssl/server.key"),a("span",{attrs:{class:"token string"}},[t._v("'\n'")]),t._v(" "),a("span",{attrs:{class:"token operator"}},[t._v(">>")]),t._v(" postgreql.conf\n")])])])},function(){var t=this.$createElement,s=this._self._c||t;return s("h2",{attrs:{id:"use-from-client"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#use-from-client","aria-hidden":"true"}},[this._v("#")]),this._v(" Use from client")])},function(){var t=this.$createElement,s=this._self._c||t;return s("div",{staticClass:"language-bash extra-class"},[s("pre",{pre:!0,attrs:{class:"language-bash"}},[s("code",[s("span",{attrs:{class:"token function"}},[this._v("cp")]),this._v(" server.crt ~/.postgresql/postgresql.crt\n"),s("span",{attrs:{class:"token function"}},[this._v("cp")]),this._v(" server.key ~/.postgresql/postgresql.key\n\n"),s("span",{attrs:{class:"token comment"}},[this._v("# connect with sslmode=require")]),this._v("\n")])])])}],!1,null,null,null);e.options.__file="postgres.md";s.default=e.exports}}]);