Last active
April 4, 2023 05:03
-
-
Save mche/02f43b6cde0f166ba06f0205f37dd7e5 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- Name: контрагенты/проверка ИНН(integer, jsonb); Type: FUNCTION; Schema: public; Owner: - | |
-- | |
CREATE FUNCTION public."контрагенты/проверка ИНН"(id integer, attrs jsonb) RETURNS boolean | |
LANGUAGE plpgsql | |
AS $_$ | |
BEGIN | |
IF $2 is null or $2->>'ИНН' is null or $2->>'ИНН'='' THEN | |
RETURN true; | |
END IF; | |
RETURN NOT EXISTS ( | |
select k.id | |
from "контрагенты" k | |
where ($1 is null or k.id!=$1) and coalesce(coalesce(k."реквизиты",'{}'::jsonb)->>'ИНН', '#'||(k.id::text))=coalesce($2,'{}'::jsonb)->>'ИНН' | |
); | |
END; | |
$_$; | |
-- | |
-- Name: контрагенты; Type: TABLE; Schema: public; Owner: - | |
-- | |
CREATE TABLE IF NOT EXISTS public."контрагенты" ( | |
id integer DEFAULT nextval('public."ИД"'::regclass) NOT NULL, | |
ts timestamp without time zone DEFAULT now() NOT NULL, | |
title text NOT NULL, | |
"реквизиты" jsonb, | |
"АТИ" text | |
---CONSTRAINT "контрагенты/проверка ИНН" CHECK (public."контрагенты/проверка ИНН"(id, "реквизиты")) | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment