Pinned

1. postgres_misc postgres_misc Public

   postgres functions, triggers misc.

   PLpgSQL 7

2. search text global. search text global.

   1

   /*

   2

   input:  search word, schemas[], verbose_is_true_or_false

   3

   output: ctids, tablename, column name.

   4

   global_search is a plpgsql function that search  _srctext in all the visible tables(table,materialized view) of the public schema (default)

   5

   if you specified schemas, then search agaginst all the visible tables in an array schemas (param schema_names).

3. passing variable while do insert ope... passing variable while do insert operation

   1

   /*

   2

   https://dba.stackexchange.com/questions/303502/pass-a-variable-with-insert-update-delete-statements/303546#303546

   3

   https://dbfiddle.uk/ZsFssnOW 

   4

    */

   5

4. WindowsFunction WindowsFunction

   1

   2

   select * from emp; -- show the whole table.

   3

   begin;

   4

   create table emp(empid int primary key, name  text, department text, salary numeric);

   5

   insert into emp(empid, name, department, salary) values

5. for timestamp range, enforce no_adja... for timestamp range, enforce no_adjacent, no_overlap, enforce_bounds constraints.

   1

   --using constraint make 2 timestamp range not overlap.

   2

   --using constraint make 2 timestamp range not adjacent.

   3

   --using constraint make rtimestamp range include range lower bound, exclude upper bound.

   4

   /*

   5

   https://dbfiddle.uk/KyOD2a58

6. select_specific_column_to_json_agg.sql select_specific_column_to_json_agg.sql

   1

   /*

   2

   https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg/69658#69658

   3

   4

   */

   5