jianhe-fun

/*
input:  search word, schemas[], verbose_is_true_or_false
output: ctids, tablename, column name.
global_search is a plpgsql function that search  _srctext in all the visible tables(table,materialized view) of the public schema (default)
if you specified schemas, then search agaginst all the visible tables in an array schemas (param schema_names).

--for each table being searched, find only 10 rows that have the searched word.
-- skipped the partititoned table.
*/
CREATE OR REPLACE FUNCTION global_search (

jianhe-fun

--https://dba.stackexchange.com/questions/324803/add-composite-primary-key-on-existing-postgresql-table/324815#324815

CREATE TABLE demo (
    a int,
    b int,
    CONSTRAINT a_b UNIQUE (a, b)
);

INSERT INTO demo VALUES (1, 2),(2, 3);

jianhe-fun

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

*/

BEGIN;
SET local search_path = '';
CREATE temp TABLE tbl_a (
    id bigint,
    name text

jianhe-fun

/*
https://stackoverflow.com/questions/5144036/escape-function-for-regular-expression-or-like-patterns/45741630#45741630

escape regular expression.
*/

CREATE OR REPLACE FUNCTION public.f_regexp_escape(text)
 RETURNS text
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE STRICT

jianhe-fun

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

CREATE TABLE ins_tbl (
    id int GENERATED ALWAYS AS IDENTITY,
    name text
);

jianhe-fun

--https://stackoverflow.com/questions/9067335/how-does-the-search-path-influence-identifier-resolution-and-the-current-schema/9067777#9067777
--properly set search path.
--Set it in cluster level.
ALTER SYSTEM SET search_path TO 'public';
SELECT pg_reload_conf();

--Set it as default for database test16.
ALTER DATABASE test16 SET search_path = '$user', 'public';

-- Set it as default for the role (bob) you connect with (effective cluster-wide):

jianhe-fun

--using constraint make 2 timestamp range not overlap.
--using constraint make 2 timestamp range not adjacent.
--using constraint make rtimestamp range include range lower bound, exclude upper bound.
/*
https://dbfiddle.uk/KyOD2a58
Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
https://stackoverflow.com/questions/19504727/preventing-adjacent-overlapping-entries-with-exclude-in-postgresql/19505869#19505869
*/
DROP TABLE test_gist_lap;
BEGIN;

jianhe-fun

/*
Find latest entries for a person_id by submission_date for specified 
filter criteria type, plan, status. 
There could be more such filters, 
but the logic to return latest by submission date is the same regardless. 
Two major uses one for paginated viewing in UI 
and second for generating reports.
https://stackoverflow.com/questions/61160156/get-paginated-rows-and-total-count-in-single-query/61160817#61160817
*/

jianhe-fun

/*
https://stackoverflow.com/questions/38297935/split-function-returned-record-into-multiple-columns
*/

create or replace function hi_lo(a numeric, 
                                 b numeric, 
                                 c numeric, 
                                 OUT hi numeric, 
                                 OUT lo numeric)
as $$

jianhe-fun

/*
https://dbfiddle.uk/DRmCu_7u
*/

begin;
create table observations(
    id bigint primary key generated always as identity
    ,station_id int
    ,create_at timestamptz
    ,foo text