mvt033WX

1database=$POSTGRES_DB
2user=$POSTGRES_USER
3[ -z "$user" ] && user="postgres"
4
5psql -v ON_ERROR_STOP=1 --username "$user" $database <<-EOSQL
6    DROP TRIGGER IF EXISTS add_transition1 ON houses;
7    CREATE TABLE IF NOT EXISTS houses (
8        id SERIAL NOT NULL,
9        name VARCHAR(80),
10        created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
11        PRIMARY KEY(id)
12    );
13
14    CREATE TABLE IF NOT EXISTS transitions1 (
15        id SERIAL NOT NULL,
16        house_id INTEGER,
17        user_id INTEGER,
18        created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
19        PRIMARY KEY(id),
20        FOREIGN KEY(house_id) REFERENCES houses (id) ON DELETE CASCADE
21
22    );
23
24    CREATE OR REPLACE FUNCTION add_transition1() RETURNS TRIGGER AS $$
25        DECLARE
26            user_id integer;
27        BEGIN
28            user_id := current_setting('myvars.user_id')::integer || NULL;
29            INSERT INTO transitions1 (user_id, house_id) VALUES (user_id, NEW.id);
30            RETURN NULL;
31        END;
32    $$ LANGUAGE plpgsql;
33
34    CREATE TRIGGER add_transition1 AFTER INSERT OR UPDATE ON houses FOR EACH ROW EXECUTE PROCEDURE add_transition1();
35
36    BEGIN;
37    %1% SELECT current_setting('myvars.user_id');
38    %2% SELECT set_config('myvars.user_id', '55', false);
39    %3% SELECT current_setting('myvars.user_id');
40    INSERT INTO houses (name) VALUES ('HOUSE PARTY') RETURNING houses.id;
41    SELECT * from houses;
42    SELECT * from transitions1;
43    COMMIT;
44    DROP TRIGGER IF EXISTS add_transition1 ON houses;
45    DROP FUNCTION IF EXISTS add_transition1;
46    DROP TABLE transitions1;
47        DROP TABLE houses;
48EOSQL