UcVrnqSy

1CREATE OR REPLACE FUNCTION rfbs_provider_template.column_exists(ptable TEXT, pcolumn TEXT)
2    RETURNS BOOLEAN AS $BODY$
3DECLARE result bool;
4BEGIN
5    SELECT COUNT(*) INTO result
6    FROM information_schema.columns
7    WHERE
8            table_schema = 'rupost_ops' and
9            table_name = ptable and
10            column_name = pcolumn;
11    RETURN result;
12END$BODY$
13    LANGUAGE plpgsql VOLATILE;
14
15-- rfbs_provider_template.provider_template
16
17CREATE TABLE IF NOT EXISTS rfbs_provider_template.provider_template
18(
19    id               BIGINT       NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
20    added_at         TIMESTAMP    NOT NULL,
21    provider_id      BIGINT       NOT NULL,
22    name             VARCHAR(128) NOT NULL,
23    delivery_type_id BIGINT       NOT NULL,
24    inner_state      INT          NOT NULL,
25    service_code     VARCHAR(8)   NOT NULL
26);
27
28DO 
29$$
30    BEGIN
31         IF NOT rfbs_provider_template.column_exists('rfbs_provider_template.provider_template', 'service_code') THEN
32             ALTER TABLE rfbs_provider_template.provider_template ADD COLUMN service_code VARCHAR(8);
33             
34             UPDATE rfbs_provider_template.provider_template SET service_code = '1' WHERE delivery_type_id = 1;
35
36             ALTER TABLE rfbs_provider_template.provider_template ALTER COLUMN service_code SET NOT NULL;
37         END IF;
38    END
39$$;