<!DOCTYPE> <html> <head> <meta charset="utf-8" /> <title>PGWeb | API -  01>B0 A A5AA8O<8</title> <style> body { font-size: 14px; font-family: sans-serif; padding: 0; margin: 0; } .root_title { padding: 10px; background: #326690; color: #fff; } .root_title b { font-size: 47px; } .lible { padding: 10px; border: solid 1px #cbf5ff; border-radius: 4px; color: #0c6080; } @media screen and (max-width: 800px) { .root_title span { display: none; } } .ctx { padding: 10px; } .lnk_root_title { color: #fff; text-decoration: none; } </style> <script src="/src/jquery-3.3.1.min.js"></script> </head> <body> <div class="root_title"><b><a class="lnk_root_title" href="/">PGWeb</a></b> <span>&minus; =51>;LH0O SQL-181;8>B5:0 4;O A>740=8O web-?@8;>65=89</span></div> <div class="ctx"> <style> pre { padding: 10px; background: #eee; overflow: auto; font-size: 14px; font-family: sans-serif; } .in[data-req=""] { font-style: italic; opacity: 0.4; } body, table { font-size: 14px; font-family: sans-serif; } .out, .in { display: inline-block; border-bottom: solid 1px; cursor: pointer; font-weight: bold; } .out { color: red; } .in { color: #47e647; } .in.active_key { color: #008800; } .out.active_key { color: #750303; } .td_func span:last-child { display: none; } .td_func { width: 40%; } .table_functions { border-collapse: collapse; } .table_functions tr td { border: solid 1px #eee; vertical-align: top; padding: 4px; } .table_functions tr:first-child td { font-weight: bold; } .table_exceptions { border-collapse: collapse; position: absolute; background: #fff; padding: 4px; width: 400px; display: none; } .table_exceptions tr td { border-color: #aaa; } .title_exceptions { display: inline-block; border-bottom: dotted 1px; color: red; cursor: pointer; font-weight: bold; } .exceptions_tr { background: #eee; } .example_warning { display: inline-block; color: #fff; margin-top: 10px; margin-left: 10px; background: #709080; padding: 4px; margin-bottom: 4px; border-radius: 2px; } </style> <h1> 01>B0 A A5AA8O<8</h1> <div class="out" data-req="">KE>4=>5 7=0G5=85</div><br/> <div class="in" data-req="1">1O70B5;L=K9 ?0@0<5B@</div><br/> <div class="in" data-req="">5>1O70B5;L=K9 ?0@0<5B@</div><br/> <br/> <table class="table_functions"> <tr> <td>$C=:F8O</td> <td>?8A0=85</td> <td>@8<5@</td> </tr> <tr> <td class="td_func"><div class="out" data-key="session_id" title="ID A5AA88">BIGINT</div> <a href="#add0" name="add0">add</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;OQ" data-key="user_ip">INET</div><span>,</span> <div class="in" data-req="" title="ID ?>;L7>20B5;O" data-key="user_id">INT</div><span>,</span> )</td> <td> >102;5=85 A5AA88 <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.add( 'qwertyuiopasdfg', '127.0.0.1'::inet );<Br/> SELECT schema_sessions.add( 'qwertyuiopasdfg', '127.0.0.1'::inet, 1 );</td> </tr> <tr> <td class="td_func"><div class="out" data-key="session_id" title="ID A5AA88">BIGINT</div> <a href="#get_id1" name="get_id1">get_id</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;OQ" data-key="user_ip">INET</div><span>,</span> )</td> <td> >;CG5=85 :;NG0 A5AA88 <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> <tr> <td><b>NOT FOUND</b></td> <td>5 =0945=></td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.get_id( 'qwertyuiopasdfg', '127.0.0.1'::inet );</td> </tr> <tr> <td class="td_func"><div class="out" data-key="" title="">VOID</div> <a href="#set2" name="set2">set</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;O" data-key="user_ip">INET</div><span>,</span> <div class="in" data-req="1" title="<O 40==KE" data-key="name_data">VARCHAR</div><span>,</span> <div class="in" data-req="" title="0==K5" data-key="val_data">JSONB</div><span>,</span> )</td> <td> #AB0=>2:0 40==KE A5AA88 <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> <tr> <td><b>NOT FOUND</b></td> <td>5 =0945=></td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.set( 'qwertyuiopasdfg', '127.0.0.1'::inet, 'name', '"Vasja"'::jsonb );<Br/> <div class="example_warning">#40;5=85 name</div><Br/> SELECT schema_sessions.set( 'qwertyuiopasdfg', '127.0.0.1'::inet, 'name', NULL::jsonb );</td> </tr> <tr> <td class="td_func"><div class="out" data-key="" title="">VOID</div> <a href="#set3" name="set3">set</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="ID A5AA88" data-key="session_id">BIGINT</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;O" data-key="user_ip">INET</div><span>,</span> <div class="in" data-req="1" title="<O 40==KE" data-key="name_data">VARCHAR</div><span>,</span> <div class="in" data-req="" title="0==K5" data-key="val_data">JSONB</div><span>,</span> )</td> <td> #AB0=>2:0 40==KE A5AA88 <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> <tr> <td><b>NOT FOUND</b></td> <td>5 =0945=></td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.set( 'qwertyuiopasdfg', 1, '127.0.0.1'::inet, 'name', '"Vasja"'::jsonb );<Br/> <div class="example_warning">#40;5=85 name</div><Br/> SELECT schema_sessions.set( 'qwertyuiopasdfg', 1, '127.0.0.1'::inet, 'name', NULL::jsonb );</td> </tr> <tr> <td class="td_func"><div class="out" data-key="val_data" title="0==K5">JSONB</div> <a href="#get4" name="get4">get</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;O" data-key="user_ip">INET</div><span>,</span> <div class="in" data-req="1" title="<O 40==KE" data-key="name_data">VARCHAR</div><span>,</span> )</td> <td> >;CG5=85 40==KE A5AA88 <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> <tr> <td><b>NOT FOUND</b></td> <td>5 =0945=></td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.get( 'qwertyuiopasdfg', '127.0.0.1'::inet, 'name' );</td> </tr> <tr> <td class="td_func"><div class="out" data-key="val_data" title="0==K5">JSONB</div> <a href="#get5" name="get5">get</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="ID A5AA88" data-key="session_id">BIGINT</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;O" data-key="user_ip">INET</div><span>,</span> <div class="in" data-req="1" title="<O 40==KE" data-key="name_data">VARCHAR</div><span>,</span> )</td> <td> >;CG5=85 40==KE A5AA88 <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> <tr> <td><b>NOT FOUND</b></td> <td>5 =0945=></td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.get( 'qwertyuiopasdfg', 1, '127.0.0.1'::inet, 'name' );</td> </tr> <tr> <td class="td_func"><div class="out" data-key="user_id" title="ID ?>;L7>20B5;O">INT</div> <a href="#get_user_id6" name="get_user_id6">get_user_id</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;O" data-key="user_ip">INET</div><span>,</span> )</td> <td> >;CG5=85 ID ?>;L7>20B5;O <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> <tr> <td><b>NOT FOUND</b></td> <td>5 =0945=></td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.get_user_id( 'qwertyuiopasdfg', '127.0.0.1'::inet );</td> </tr> <tr> <td class="td_func"><div class="out" data-key="user_id" title="ID ?>;L7>20B5;O">INT</div> <a href="#get_user_id7" name="get_user_id7">get_user_id</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="ID A5AA88" data-key="session_id">BIGINT</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;O" data-key="user_ip">INET</div><span>,</span> )</td> <td> >;CG5=85 ID ?>;L7>20B5;O <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> <tr> <td><b>NOT FOUND</b></td> <td>5 =0945=></td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.get_user_id( 'qwertyuiopasdfg', 1, '127.0.0.1'::inet );</td> </tr> <tr> <td class="td_func"><div class="out" data-key="time_end" title=">=5F 687=8 A5AA88">TIMESTAMP</div> <a href="#get_time_end8" name="get_time_end8">get_time_end</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;O" data-key="user_ip">INET</div><span>,</span> )</td> <td> >;CG5=85 2@5<5=8, :>340 A5AA8O 8AB5:05B <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> <tr> <td><b>NOT FOUND</b></td> <td>5 =0945=></td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.get_time_end( 'qwertyuiopasdfg', '127.0.0.1'::inet );</td> </tr> <tr> <td class="td_func"><div class="out" data-key="time_end" title=">=5F 687=8 A5AA88">TIMESTAMP</div> <a href="#get_time_end9" name="get_time_end9">get_time_end</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="ID A5AA88" data-key="session_id">BIGINT</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;O" data-key="user_ip">INET</div><span>,</span> )</td> <td> >;CG5=85 2@5<5=8, :>340 A5AA8O 8AB5:05B <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> <tr> <td><b>NOT FOUND</b></td> <td>5 =0945=></td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.get_time_end( 'qwertyuiopasdfg', 1, '127.0.0.1'::inet );</td> </tr> <tr> <td class="td_func"><div class="out" data-key="" title="">VOID</div> <a href="#remove10" name="remove10">remove</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;O" data-key="user_ip">INET</div><span>,</span> )</td> <td> #40;5=85 A5AA88 <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> <tr> <td><b>NOT FOUND</b></td> <td>5 =0945=></td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.remove( 'qwertyuiopasdfg', '127.0.0.1'::inet );</td> </tr> <tr> <td class="td_func"><div class="out" data-key="" title="">VOID</div> <a href="#remove11" name="remove11">remove</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="ID A5AA88" data-key="session_id">BIGINT</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;O" data-key="user_ip">INET</div><span>,</span> )</td> <td> #40;5=85 A5AA88 <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> <tr> <td><b>NOT FOUND</b></td> <td>5 =0945=></td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.remove( 'qwertyuiopasdfg', 1, '127.0.0.1'::inet );</td> </tr> <tr> <td class="td_func"><div class="out" data-key="" title="">VOID</div> <a href="#set_prolong12" name="set_prolong12">set_prolong</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;O" data-key="user_ip">INET</div><span>,</span> <div class="in" data-req="1" title=">;8G5AB2> A5:C=4" data-key="seconds">INT</div><span>,</span> )</td> <td> @>4;5=85 687=8 A5AA88 <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> <tr> <td><b>NOT FOUND</b></td> <td>5 =0945=></td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.set_prolong( 'qwertyuiopasdfg', '127.0.0.1'::inet, 100 );<br/> <div class="example_warning">A;8 :>;8G5AB2> A5:C=4 @02=> 0, B> A5AA8O BCB 65 AB0=5B =50:B82=>9</div></td> </tr> <tr> <td class="td_func"><div class="out" data-key="" title="">VOID</div> <a href="#set_prolong13" name="set_prolong13">set_prolong</a> ( <div class="in" data-req="1" title=";NG A5AA88" data-key="session_key">VARCHAR</div><span>,</span> <div class="in" data-req="1" title="ID A5AA88" data-key="session_id">BIGINT</div><span>,</span> <div class="in" data-req="1" title="IP-04@5A ?>;L7>20B5;O" data-key="user_ip">INET</div><span>,</span> <div class="in" data-req="1" title=">;8G5AB2> A5:C=4" data-key="seconds">INT</div><span>,</span> )</td> <td> @>4;5=85 687=8 A5AA88 <div> <div class="title_exceptions">A:;NG5=8O</div> <table class="table_exceptions"> <tr> <td>;NG</td> <td>?8A0=85</td> </tr> <tr> <td><b>WRONG PARAMS</b></td> <td>5?@028;L=K5 ?0@0<5B@K</td> </tr> <tr> <td><b>NOT FOUND</b></td> <td>5 =0945=></td> </tr> </table> </div> </td> <td style="width:35%">SELECT schema_sessions.set_prolong( 'qwertyuiopasdfg', 1, '127.0.0.1'::inet, 100 );<br/> <div class="example_warning">A;8 :>;8G5AB2> A5:C=4 @02=> 0, B> A5AA8O BCB 65 AB0=5B =50:B82=>9</div></td> </tr> <tr> <td class="td_func"><div class="out" data-key="" title="">VOID</div> <a href="#clear14" name="clear14">clear</a> ( )</td> <td> #40;5=85 2A5E =50:B82=KE A5AA89 </td> <td style="width:35%">SELECT schema_sessions.clear();</td> </tr> <tr> <td class="td_func"><div class="out" data-key="" title="">VOID</div> <a href="#set_timelife15" name="set_timelife15">set_timelife</a> ( <div class="in" data-req="1" title=">;8G5AB2> A5:C=4" data-key="seconds">INT</div><span>,</span> )</td> <td> #AB0=>2:0 45D>;B=>3> 2@5<5=8 687=8 4;O 2=>2L A>740205<KE A5AA89 </td> <td style="width:35%">SELECT schema_sessions.set_timelife( 100 );</td> </tr> </table> <h2 style="margin-top:50px">SQL</h2> <label><O AE5<K: <input value="schema_sessions" class="schema_name" /> <pre> CREATE SCHEMA IF NOT EXISTS schema_sessions; -- TABLES CREATE TABLE schema_sessions._sessions_table_settings( lifetime INT NOT NULL DEFAULT 3600 * 24, CHECK ( lifetime &gt;= 0 ) ); INSERT INTO schema_sessions._sessions_table_settings( lifetime ) VALUES ( 3600 * 24 ); CREATE TABLE schema_sessions._sessions_table_values( id BIGSERIAL, part varchar(1) NOT NULL, prefix varchar(10) NOT NULL, value varchar NOT NULL, time_end timestamp NOT NULL, user_id int, data jsonb DEFAULT '{}'::jsonb, ip inet NOT NULL ) PARTITION BY LIST(part); CREATE OR REPLACE FUNCTION schema_sessions._build_parts() RETURNS void AS $$ DECLARE var_arr varchar[] := ARRAY[ 'q', 'w', 'e', 'r', 't', 'y', 'i', 'o', 'p', 'a', 's', 'd', 'f', 'g', 'h', 'j', 'k', 'l', 'z', 'x', 'c', 'v', 'b', 'n', 'm', '_Q', '_W', '_E', '_R', '_T', '_Y', '_I', '_O', '_P', '_A', '_S', '_D', '_F', '_G', '_H', '_J', '_K', '_L', '_Z', '_X', '_C', '_V', '_B', '_N', '_M', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ]; var_arr_char varchar[] := ARRAY[ 'q', 'w', 'e', 'r', 't', 'y', 'i', 'o', 'p', 'a', 's', 'd', 'f', 'g', 'h', 'j', 'k', 'l', 'z', 'x', 'c', 'v', 'b', 'n', 'm', 'Q', 'W', 'E', 'R', 'T', 'Y', 'I', 'O', 'P', 'A', 'S', 'D', 'F', 'G', 'H', 'J', 'K', 'L', 'Z', 'X', 'C', 'V', 'B', 'N', 'M', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ]; var_length_arr int := array_length( var_arr, 1 ); i int; BEGIN FOR i IN 1..var_length_arr LOOP EXECUTE 'CREATE TABLE schema_sessions.part_' || var_arr[i] || ' partition OF schema_sessions._sessions_table_values FOR VALUES IN('''|| var_arr_char[i] || ''');'; EXECUTE 'CREATE INDEX ON schema_sessions.part_' || var_arr[i] || '( id );'; EXECUTE 'CREATE INDEX ON schema_sessions.part_' || var_arr[i] || '( prefix );'; END LOOP; EXECUTE 'DROP FUNCTION schema_sessions._build_parts()'; END; $$ LANGUAGE plpgsql; -- FUNCTIONS CREATE OR REPLACE FUNCTION schema_sessions._sessions_get_end_time() RETURNS timestamp AS $$ DECLARE ret_end_time timestamp := clock_timestamp(); seconds int; BEGIN SELECT lifetime INTO seconds FROM schema_sessions._sessions_table_settings; RETURN ret_end_time + ( seconds || ' seconds' )::interval; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions._get_session( p_key varchar, p_id BIGINT, p_ip inet ) RETURNS RECORD AS $$ DECLARE var_value VARCHAR; var_prefix VARCHAR(10); var_part VARCHAR; var_ret RECORD; BEGIN IF p_ip IS NULL OR p_key IS NULL THEN RAISE EXCEPTION 'WRONG PARAMS'; END IF; IF p_key ~ '^[a-zA-Z0-9]{15,}$' = FALSE THEN RAISE EXCEPTION 'WRONG PARAMS'; END IF; var_prefix := substring( p_key from 1 for 10 ); var_part := substring(var_prefix from 10 for 1); var_value := substring( p_key from 11 for ( char_length( p_key ) - 10 )::int ); IF p_id IS NOT NULL THEN SELECT * INTO var_ret FROM schema_sessions._sessions_table_values WHERE part = var_part AND prefix = var_prefix AND value = var_value AND ip = p_ip AND id = p_id AND time_end &gt;= clock_timestamp(); ELSE SELECT * INTO var_ret FROM schema_sessions._sessions_table_values WHERE part = var_part AND prefix = var_prefix AND value = var_value AND ip = p_ip AND time_end &gt;= clock_timestamp(); END IF; IF var_ret IS NULL THEN RAISE EXCEPTION 'NOT FOUND'; END IF; RETURN var_ret; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.add( p_key varchar, p_ip inet, p_user_id int = null ) RETURNS bigint AS $$ DECLARE var_value varchar; var_prefix varchar(10); var_time_end timestamp; search_row RECORD; BEGIN IF p_key IS NULL OR p_ip IS NULL OR p_key ~ '^[a-zA-Z0-9]{15,}$' = FALSE THEN RAISE EXCEPTION 'WRONG PARAMS'; END IF; var_prefix := substring( p_key from 1 for 10 ); var_value := substring( p_key from 11 for ( char_length( p_key ) - 10 )::int ); var_time_end := schema_sessions._sessions_get_end_time(); FOR search_row IN INSERT INTO schema_sessions._sessions_table_values( part, prefix, value, time_end, ip, user_id ) VALUES ( substring(var_prefix from 10 for 1), var_prefix, var_value, var_time_end, p_ip, p_user_id ) RETURNING * LOOP RETURN search_row.id; END LOOP; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions._set( p_key varchar, p_id bigint, p_ip inet, p_key_data varchar, p_value_data jsonb ) RETURNS void AS $$ DECLARE var_session RECORD; BEGIN var_session := schema_sessions._get_session( p_key::varchar, p_id, p_ip ); IF p_value_data IS NOT NULL THEN UPDATE schema_sessions._sessions_table_values SET data = data || jsonb_build_object( p_key_data, p_value_data ) WHERE part = var_session.part AND id = var_session.id; ELSE UPDATE schema_sessions._sessions_table_values SET data = data - p_key_data WHERE part = var_session.part AND id = var_session.id; END IF; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.set( p_key varchar, p_ip inet, p_key_data varchar, p_value_data jsonb = null) RETURNS void AS $$ BEGIN PERFORM schema_sessions._set( p_key, NULL, p_ip, p_key_data, p_value_data ); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.set( p_key varchar, p_id bigint, p_ip inet, p_key_data varchar, p_value_data jsonb = null) RETURNS void AS $$ BEGIN PERFORM schema_sessions._set( p_key, p_id, p_ip, p_key_data, p_value_data ); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions._get( p_key varchar, p_id bigint, p_ip inet, p_key_data varchar ) RETURNS jsonb AS $$ DECLARE var_ret jsonb; var_session RECORD; BEGIN var_session := schema_sessions._get_session( p_key::varchar, p_id, p_ip ); SELECT data-&gt;p_key_data INTO var_ret FROM schema_sessions._sessions_table_values WHERE part = var_session.part AND id = var_session.id AND time_end &gt;= clock_timestamp(); RETURN var_ret; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.get( p_key varchar, p_ip inet, p_key_data varchar ) RETURNS JSONB AS $$ BEGIN RETURN schema_sessions._get( p_key, NULL, p_ip, p_key_data ); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.get( p_key varchar, p_id BIGINT, p_ip inet, p_key_data varchar ) RETURNS JSONB AS $$ BEGIN RETURN schema_sessions._get( p_key, p_id, p_ip, p_key_data ); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.get_id( p_key varchar, p_ip inet ) RETURNS bigint AS $$ DECLARE var_session RECORD; BEGIN var_session := schema_sessions._get_session( p_key, NULL, p_ip ); RETURN var_session.id; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions._get_user_id( p_key varchar, p_id BIGINT, p_ip inet ) RETURNS int AS $$ DECLARE var_session RECORD; BEGIN var_session := schema_sessions._get_session( p_key, p_id, p_ip ); RETURN var_session.user_id; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.get_user_id( p_key varchar, p_id BIGINT, p_ip inet ) RETURNS int AS $$ BEGIN RETURN schema_sessions._get_user_id( p_key, p_id, p_ip ); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.get_user_id( p_key varchar, p_ip inet ) RETURNS int AS $$ BEGIN RETURN schema_sessions._get_user_id( p_key, NULL, p_ip ); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions._get_time_end( p_key varchar, p_id BIGINT, p_ip inet ) RETURNS timestamp AS $$ DECLARE var_session RECORD; BEGIN var_session := schema_sessions._get_session( p_key, p_id, p_ip ); RETURN var_session.time_end; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.get_time_end( p_key varchar, p_id BIGINT, p_ip inet ) RETURNS timestamp AS $$ BEGIN RETURN schema_sessions._get_time_end( p_key, p_id, p_ip ); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.get_time_end( p_key varchar, p_ip inet ) RETURNS timestamp AS $$ BEGIN RETURN schema_sessions._get_time_end( p_key, NULL, p_ip ); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions._remove( p_key varchar, p_id BIGINT, p_ip inet ) RETURNS void AS $$ DECLARE var_session RECORD; BEGIN var_session := schema_sessions._get_session( p_key, p_id, p_ip ); DELETE FROM schema_sessions._sessions_table_values WHERE part = var_session.part AND id = var_session.id; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.remove( p_key varchar, p_id BIGINT, p_ip inet ) RETURNS void AS $$ BEGIN PERFORM schema_sessions._remove( p_key, p_id, p_ip ); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.remove( p_key varchar, p_ip inet ) RETURNS void AS $$ BEGIN PERFORM schema_sessions._remove( p_key, NULL, p_ip ); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.clear() RETURNS void AS $$ BEGIN DELETE FROM schema_sessions._sessions_table_values WHERE time_end &lt; clock_timestamp(); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions._set_prolong( p_key varchar, p_id BIGINT, p_ip inet, p_seconds INT ) RETURNS void AS $$ DECLARE var_session RECORD; var_time_end timestamp := clock_timestamp() + ( p_seconds || ' seconds' )::interval; BEGIN var_session := schema_sessions._get_session( p_key, p_id, p_ip ); IF p_seconds IS NULL OR p_seconds &lt; 0 THEN RAISE EXCEPTION 'WRONG PARAMS'; END IF; UPDATE schema_sessions._sessions_table_values SET time_end = var_time_end WHERE part = var_session.part AND id = var_session.id; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.set_prolong( p_key varchar, p_id BIGINT, p_ip inet, p_seconds INT ) RETURNS void AS $$ BEGIN PERFORM schema_sessions._set_prolong( p_key, p_id, p_ip, p_seconds ); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.set_prolong( p_key varchar, p_ip inet, p_seconds INT ) RETURNS void AS $$ BEGIN PERFORM schema_sessions._set_prolong( p_key, NULL, p_ip, p_seconds ); END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION schema_sessions.set_timelife( p_seconds int ) RETURNS void AS $$ BEGIN UPDATE schema_sessions._sessions_table_settings SET lifetime = p_seconds; END; $$ LANGUAGE plpgsql; SELECT schema_sessions._build_parts(); </pre> <script defer> function init() { var name = $( '.schema_name' ).val(); var html = $( 'pre' ).text(); html = html.replace( new RegExp( name+'\\.', 'g' ), '<span>'+name+'</span>.' ); html = html.replace( new RegExp( name+';', 'g' ), '<span>'+name+'</span>;' ); $( 'pre' ).html( html ); $( '.schema_name' ).on( 'input', function() { if( $( this ).val() == '' ) { $( 'pre span' ).html( name ); } else { $( 'pre span' ).html( $( this ).val() ); } }); } init(); // http://jsfiddle.net/zAZyy/ ;-) jQuery.fn.selectText = function(){ var doc = document; var element = this[0]; if (doc.body.createTextRange) { var range = document.body.createTextRange(); range.moveToElementText(element); range.select(); } else if (window.getSelection) { var selection = window.getSelection(); var range = document.createRange(); range.selectNodeContents(element); selection.removeAllRanges(); selection.addRange(range); } }; $( 'pre' ).on( 'click', function() { $( this ).selectText(); }); $( '[data-key]' ).on( 'mouseover', function() { var attr = $( this ).attr( 'data-key' ); if( attr != '' ) { $( '[data-key="'+attr+'"]' ).addClass( 'active_key' ); } }); $( '[data-key]' ).on( 'mouseout', function() { var attr = $( this ).attr( 'data-key' ); if( attr != '' ) { $( '[data-key="'+attr+'"]' ).removeClass( 'active_key' ); } }); $( '.title_exceptions' ).on( 'click', function() { if( $( this ).closest( 'tr' ).hasClass( 'exceptions_tr' ) ) { $( '.table_exceptions' ).hide(); $( '.exceptions_tr' ).removeClass( 'exceptions_tr' ); } else { $( '.table_exceptions' ).hide(); $( '.exceptions_tr' ).removeClass( 'exceptions_tr' ); $( this ).closest( 'td' ).find( '.table_exceptions' ).css( { display: 'table' } ); $( this ).closest( 'tr' ).addClass( 'exceptions_tr' ); } return false; }); $( '.table_exceptions' ).on( 'click', function() { return false; }); $( document ).on( 'click', function() { $( '.table_exceptions' ).hide(); $( '.exceptions_tr' ).removeClass( 'exceptions_tr' ); }); </script> </div> </body> </html>