zentao/db/pgsql_function.sql

CREATE OR REPLACE FUNCTION FIND_IN_SET(
  target text,
  list text
) RETURNS boolean AS $$
DECLARE
  arr text[];
  i integer;
BEGIN
  IF list IS NULL OR list = '' THEN
    RETURN false;
  END IF;

  arr := string_to_array(list, ',');

  FOR i IN 1..array_length(arr, 1) LOOP
    IF arr[i] = target THEN
      RETURN true;
    END IF;
  END LOOP;

  RETURN false;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION FIND_IN_SET(
  target integer,
  list text
) RETURNS boolean AS $$
BEGIN
  RETURN FIND_IN_SET(target::text, list);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION GROUP_CONCAT(
    input text,
    delimiter text = ','
) RETURNS text AS $$
BEGIN
    RETURN string_agg(input, delimiter);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION GROUP_CONCAT(
    input numeric,
    delimiter text = ','
) RETURNS text AS $$
BEGIN
    RETURN string_agg(input::text, delimiter);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION GROUP_CONCAT(
    input integer,
    delimiter text = ','
) RETURNS text AS $$
BEGIN
    RETURN string_agg(input::text, delimiter);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION ROUND(
    num float,
    decimals integer
) RETURNS numeric AS $$
BEGIN
    RETURN ROUND(num::numeric, decimals);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION ROUND(
    num double precision,
    decimals integer
) RETURNS numeric AS $$
BEGIN
    RETURN ROUND(num::numeric, decimals);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION ROUND(
    num text,
    decimals integer
) RETURNS numeric AS $$
BEGIN
    RETURN ROUND(num::numeric, decimals);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION TIMESTAMPDIFF(
    unit TEXT,
    start_time TIMESTAMP,
    end_time TIMESTAMP
) RETURNS INTEGER AS $$
DECLARE
    diff_interval INTERVAL;
    diff_seconds NUMERIC;
BEGIN
    diff_interval := end_time - start_time;

    CASE lower(unit)
        WHEN 'year' THEN
            RETURN EXTRACT(YEAR FROM end_time) - EXTRACT(YEAR FROM start_time)
                - CASE WHEN (EXTRACT(MONTH FROM end_time) < EXTRACT(MONTH FROM start_time))
                        OR (EXTRACT(MONTH FROM end_time) = EXTRACT(MONTH FROM start_time)
                            AND EXTRACT(DAY FROM end_time) < EXTRACT(DAY FROM start_time))
                      THEN 1 ELSE 0 END;
        WHEN 'month' THEN
            RETURN (EXTRACT(YEAR FROM end_time) - EXTRACT(YEAR FROM start_time)) * 12
                + (EXTRACT(MONTH FROM end_time) - EXTRACT(MONTH FROM start_time))
                - CASE WHEN EXTRACT(DAY FROM end_time) < EXTRACT(DAY FROM start_time)
                      THEN 1 ELSE 0 END;
        WHEN 'day' THEN
            RETURN EXTRACT(DAY FROM diff_interval);
        WHEN 'hour' THEN
            diff_seconds := EXTRACT(EPOCH FROM diff_interval);
            RETURN floor(diff_seconds / 3600);
        WHEN 'minute' THEN
            diff_seconds := EXTRACT(EPOCH FROM diff_interval);
            RETURN floor(diff_seconds / 60);
        WHEN 'second' THEN
            RETURN EXTRACT(EPOCH FROM diff_interval);
        ELSE
            RETURN NULL;
    END CASE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION IFNULL(bigint, integer)
RETURNS bigint AS $$
BEGIN
  RETURN COALESCE($1, $2::bigint);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION IFNULL(double precision, integer)
RETURNS double precision AS $$
BEGIN
  RETURN COALESCE($1, $2::double precision);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION IFNULL(integer, bigint)
RETURNS bigint AS $$
BEGIN
  RETURN COALESCE($1::bigint, $2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION IFNULL(bigint, bigint)
RETURNS bigint AS $$
BEGIN
  RETURN COALESCE($1, $2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION IFNULL(integer, integer)
RETURNS integer AS $$
BEGIN
  RETURN COALESCE($1, $2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION IFNULL(numeric, numeric)
RETURNS numeric AS $$
BEGIN
  RETURN COALESCE($1, $2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION IFNULL(text, text)
RETURNS text AS $$
BEGIN
  RETURN COALESCE($1, $2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION day(date_val DATE)
RETURNS INTEGER AS $$ BEGIN RETURN EXTRACT(DAY FROM date_val)::INTEGER; END; $$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION day(date_val TIMESTAMP)
RETURNS INTEGER AS $$ BEGIN RETURN EXTRACT(DAY FROM date_val)::INTEGER; END; $$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION day(date_val TIMESTAMPTZ)
RETURNS INTEGER AS $$ BEGIN RETURN EXTRACT(DAY FROM date_val)::INTEGER; END; $$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION MONTH(date_val DATE)
RETURNS INTEGER AS $$ BEGIN RETURN EXTRACT(MONTH FROM date_val)::INTEGER; END; $$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION MONTH(date_val TIMESTAMP)
RETURNS INTEGER AS $$ BEGIN RETURN EXTRACT(MONTH FROM date_val)::INTEGER; END; $$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION MONTH(date_val TIMESTAMPTZ)
RETURNS INTEGER AS $$ BEGIN RETURN EXTRACT(MONTH FROM date_val)::INTEGER; END; $$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION YEAR(date_val DATE)
RETURNS INTEGER AS $$ BEGIN RETURN EXTRACT(YEAR FROM date_val)::INTEGER; END; $$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION YEAR(date_val TIMESTAMP)
RETURNS INTEGER AS $$ BEGIN RETURN EXTRACT(YEAR FROM date_val)::INTEGER; END; $$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION YEAR(date_val TIMESTAMPTZ)
RETURNS INTEGER AS $$ BEGIN RETURN EXTRACT(YEAR FROM date_val)::INTEGER; END; $$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION DATEDIFF(
    end_date ANYELEMENT,
    start_date ANYELEMENT
) RETURNS INTEGER AS $$
BEGIN
    RETURN (end_date - start_date)::INTEGER;
END;
$$ LANGUAGE plpgsql;
--

CREATE OR REPLACE FUNCTION "IF"(
    condition BOOLEAN,
    true_val BOOLEAN,
    false_val BOOLEAN
) RETURNS BOOLEAN AS $$
BEGIN
    IF condition THEN
        RETURN true_val;
    ELSE
        RETURN false_val;
    END IF;
END;
$$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION "IF"(
    condition BOOLEAN,
    true_val DOUBLE PRECISION,
    false_val INTEGER
) RETURNS DOUBLE PRECISION AS $$
BEGIN
    IF condition THEN
        RETURN true_val;
    ELSE
        RETURN false_val::DOUBLE PRECISION;
    END IF;
END;
$$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION "IF"(
    condition BOOLEAN,
    true_val DOUBLE PRECISION,
    false_val DOUBLE PRECISION
) RETURNS DOUBLE PRECISION AS $$
BEGIN
    IF condition THEN
        RETURN true_val;
    ELSE
        RETURN false_val;
    END IF;
END;
$$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION "IF"(
    condition BOOLEAN,
    true_val TEXT,
    false_val TEXT
) RETURNS TEXT AS $$
BEGIN
    IF condition THEN
        RETURN true_val;
    ELSE
        RETURN false_val;
    END IF;
END;
$$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION "IF"(
    condition BOOLEAN,
    true_val INTEGER,
    false_val INTEGER
) RETURNS INTEGER AS $$
BEGIN
    IF condition THEN
        RETURN true_val;
    ELSE
        RETURN false_val;
    END IF;
END;
$$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION "IF"(
    condition BOOLEAN,
    true_val TEXT,
    false_val DATE
) RETURNS DATE AS $$
BEGIN
    IF condition THEN
        RETURN true_val;
    ELSE
        RETURN false_val::DATE;
    END IF;
END;
$$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION "IF"(
    condition BOOLEAN,
    true_val DATE,
    false_val DATE
) RETURNS DATE AS $$
BEGIN
    IF condition THEN
        RETURN true_val;
    ELSE
        RETURN false_val;
    END IF;
END;
$$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION "IF"(
    condition BOOLEAN,
    true_val  timestamp without time zone,
    false_val DATE
) RETURNS DATE AS $$
BEGIN
    IF condition THEN
        RETURN true_val::DATE;
    ELSE
        RETURN false_val;
    END IF;
END;
$$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION "IF"(
    condition BOOLEAN,
    true_val  timestamp without time zone,
    false_val timestamp without time zone
) RETURNS DATE AS $$
BEGIN
    IF condition THEN
        RETURN true_val;
    ELSE
        RETURN false_val;
    END IF;
END;
$$ LANGUAGE plpgsql;

--

CREATE OR REPLACE FUNCTION DATE_FORMAT(
    date_val DATE,
    format_str TEXT
) RETURNS TEXT AS $$
DECLARE
    pg_format TEXT;
BEGIN
    pg_format := REPLACE(format_str, '%Y', 'YYYY');
    pg_format := REPLACE(pg_format, '%y', 'YY');
    pg_format := REPLACE(pg_format, '%m', 'MM');
    pg_format := REPLACE(pg_format, '%c', 'MM');
    pg_format := REPLACE(pg_format, '%d', 'DD');
    pg_format := REPLACE(pg_format, '%e', 'DD');
    pg_format := REPLACE(pg_format, '%H', 'HH24');
    pg_format := REPLACE(pg_format, '%h', 'HH12');
    pg_format := REPLACE(pg_format, '%i', 'MI');
    pg_format := REPLACE(pg_format, '%s', 'SS');
    pg_format := REPLACE(pg_format, '%W', 'Day');
    pg_format := REPLACE(pg_format, '%a', 'Dy');
    pg_format := REPLACE(pg_format, '%M', 'Month');
    pg_format := REPLACE(pg_format, '%b', 'Mon');

    RETURN TO_CHAR(date_val, pg_format);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION instr(
    str TEXT,
    sub_str TEXT
)
RETURNS INTEGER AS $$
BEGIN
    IF str IS NULL OR sub_str IS NULL OR sub_str = '' THEN
        RETURN 0;
    END IF;

    RETURN strpos(LOWER(str), LOWER(sub_str));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION left(
    str TEXT,
    length INT
)
RETURNS TEXT AS $$
BEGIN
    IF str IS NULL OR length IS NULL THEN
        RETURN NULL;
    END IF;
    IF length <= 0 THEN
        RETURN '';
    END IF;
    RETURN pg_catalog.left(str, length);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION left(
    str DATE,
    length INT
)
RETURNS TEXT AS $$
BEGIN
    RETURN left(str::TEXT, length);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION left(
    str NUMERIC,
    length INT
)
RETURNS TEXT AS $$
BEGIN
    RETURN left(str::TEXT, length);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION left(
    str TIMESTAMP,
    length INT
)
RETURNS TEXT AS $$
BEGIN
    RETURN left(str::TEXT, length);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION date_format(
    input_date TIMESTAMP WITHOUT TIME ZONE,
    format_str TEXT
) RETURNS TEXT AS $$
DECLARE
    mapped_format TEXT := format_str;
BEGIN
    mapped_format := REPLACE(mapped_format, '%Y', 'YYYY');
    mapped_format := REPLACE(mapped_format, '%y', 'YY');
    mapped_format := REPLACE(mapped_format, '%m', 'MM');
    mapped_format := REPLACE(mapped_format, '%c', 'FM MM');
    mapped_format := REPLACE(mapped_format, '%d', 'DD');
    mapped_format := REPLACE(mapped_format, '%e', 'FM DD');
    mapped_format := REPLACE(mapped_format, '%H', 'HH24');
    mapped_format := REPLACE(mapped_format, '%h', 'HH12');
    mapped_format := REPLACE(mapped_format, '%i', 'MI');
    mapped_format := REPLACE(mapped_format, '%s', 'SS');
    mapped_format := REPLACE(mapped_format, '%W', 'Day');
    mapped_format := REPLACE(mapped_format, '%w', 'D');
    mapped_format := REPLACE(mapped_format, '%M', 'Month');
    mapped_format := REPLACE(mapped_format, '%b', 'Mon');
    mapped_format := REPLACE(mapped_format, '%p', 'AM');
    mapped_format := REPLACE(mapped_format, '%T', 'HH24:MI:SS');
    mapped_format := REPLACE(mapped_format, '%j', 'DDD');

    mapped_format := REPLACE(mapped_format, 'FM MM', 'FMMM');
    mapped_format := REPLACE(mapped_format, 'FM DD', 'FMDD');

    RETURN TO_CHAR(input_date, mapped_format);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

--

CREATE OR REPLACE FUNCTION update_tables_seq ( )
RETURNS void AS $$
	DECLARE
	seq_name_cursor CURSOR FOR
	SELECT a.attname AS column_name, c.relname AS table_name, s.relname AS seq_name
    FROM pg_class c
    JOIN pg_depend d ON d.refobjid = c.oid
    JOIN pg_class s ON d.objid = s.oid
    JOIN pg_namespace n ON s.relnamespace = n.oid
    JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = d.refobjsubid
    WHERE d.classid = 'pg_class'::regclass
      AND d.refclassid = 'pg_class'::regclass
      AND s.relkind = 'S'
      AND n.nspname = 'public';

	prepared_sql VARCHAR(255);

BEGIN
  FOR ref_record in seq_name_cursor LOOP
	prepared_sql := 'SELECT setval( ''' || ref_record.seq_name;
	prepared_sql := prepared_sql || ''','||'( SELECT MAX ( ' || ref_record.column_name || ' ) FROM "'||ref_record.table_name||'") );';
	RAISE NOTICE '%', prepared_sql;
	EXECUTE prepared_sql;
	END LOOP;
END;
$$ LANGUAGE plpgsql;