577 lines
20 KiB
Elixir
577 lines
20 KiB
Elixir
defmodule Mv.Repo.Migrations.AddCountryToMembers do
|
|
@moduledoc """
|
|
Adds country as an optional member field and includes it in full-text search.
|
|
|
|
- Adds :country column to members table (text, nullable)
|
|
- Updates members_search_vector_trigger() to include country (weight C)
|
|
- Updates update_member_search_vector_from_custom_field_value() to include country
|
|
- Updates update_member_search_vector_from_member_groups() to include country
|
|
- Backfills existing members' search_vector with country
|
|
"""
|
|
|
|
use Ecto.Migration
|
|
|
|
def up do
|
|
alter table(:members) do
|
|
add :country, :text
|
|
end
|
|
|
|
# 1. Main trigger on members: add country to search_vector
|
|
execute("""
|
|
CREATE OR REPLACE FUNCTION members_search_vector_trigger() RETURNS trigger AS $$
|
|
DECLARE
|
|
custom_values_text text;
|
|
groups_text text;
|
|
BEGIN
|
|
SELECT string_agg(
|
|
CASE
|
|
WHEN value ? '_union_value' THEN value->>'_union_value'
|
|
WHEN value ? 'value' THEN value->>'value'
|
|
ELSE ''
|
|
END,
|
|
' '
|
|
)
|
|
INTO custom_values_text
|
|
FROM custom_field_values
|
|
WHERE member_id = NEW.id AND value IS NOT NULL;
|
|
|
|
SELECT string_agg(g.name, ' ')
|
|
INTO groups_text
|
|
FROM member_groups mg
|
|
JOIN groups g ON g.id = mg.group_id
|
|
WHERE mg.member_id = NEW.id;
|
|
|
|
NEW.search_vector :=
|
|
setweight(to_tsvector('simple', coalesce(NEW.first_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.last_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.email, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.join_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.exit_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.notes, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.city, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.street, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.house_number::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.postal_code::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.country, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(custom_values_text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(groups_text, '')), 'B');
|
|
RETURN NEW;
|
|
END
|
|
$$ LANGUAGE plpgsql;
|
|
""")
|
|
|
|
# 2. Custom field trigger: include country in recomputed search_vector
|
|
execute("""
|
|
CREATE OR REPLACE FUNCTION update_member_search_vector_from_custom_field_value() RETURNS trigger AS $$
|
|
DECLARE
|
|
member_id_val uuid;
|
|
member_first_name text;
|
|
member_last_name text;
|
|
member_email text;
|
|
member_join_date date;
|
|
member_exit_date date;
|
|
member_notes text;
|
|
member_city text;
|
|
member_street text;
|
|
member_house_number text;
|
|
member_postal_code text;
|
|
member_country text;
|
|
custom_values_text text;
|
|
groups_text text;
|
|
old_value_text text;
|
|
new_value_text text;
|
|
BEGIN
|
|
member_id_val := COALESCE(NEW.member_id, OLD.member_id);
|
|
|
|
IF TG_OP = 'UPDATE' THEN
|
|
old_value_text := COALESCE(
|
|
NULLIF(OLD.value->>'_union_value', ''),
|
|
NULLIF(OLD.value->>'value', ''),
|
|
''
|
|
);
|
|
new_value_text := COALESCE(
|
|
NULLIF(NEW.value->>'_union_value', ''),
|
|
NULLIF(NEW.value->>'value', ''),
|
|
''
|
|
);
|
|
IF (old_value_text IS NOT DISTINCT FROM new_value_text) AND
|
|
(OLD.member_id IS NOT DISTINCT FROM NEW.member_id) AND
|
|
(OLD.custom_field_id IS NOT DISTINCT FROM NEW.custom_field_id) THEN
|
|
RETURN COALESCE(NEW, OLD);
|
|
END IF;
|
|
END IF;
|
|
|
|
SELECT
|
|
first_name,
|
|
last_name,
|
|
email,
|
|
join_date,
|
|
exit_date,
|
|
notes,
|
|
city,
|
|
street,
|
|
house_number,
|
|
postal_code,
|
|
country
|
|
INTO
|
|
member_first_name,
|
|
member_last_name,
|
|
member_email,
|
|
member_join_date,
|
|
member_exit_date,
|
|
member_notes,
|
|
member_city,
|
|
member_street,
|
|
member_house_number,
|
|
member_postal_code,
|
|
member_country
|
|
FROM members
|
|
WHERE id = member_id_val;
|
|
|
|
SELECT string_agg(
|
|
CASE
|
|
WHEN value ? '_union_value' THEN value->>'_union_value'
|
|
WHEN value ? 'value' THEN value->>'value'
|
|
ELSE ''
|
|
END,
|
|
' '
|
|
)
|
|
INTO custom_values_text
|
|
FROM custom_field_values
|
|
WHERE member_id = member_id_val AND value IS NOT NULL;
|
|
|
|
SELECT string_agg(g.name, ' ')
|
|
INTO groups_text
|
|
FROM member_groups mg
|
|
JOIN groups g ON g.id = mg.group_id
|
|
WHERE mg.member_id = member_id_val;
|
|
|
|
UPDATE members
|
|
SET search_vector =
|
|
setweight(to_tsvector('simple', coalesce(member_first_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(member_last_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(member_email, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(member_join_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(member_exit_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(member_notes, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(member_city, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_street, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_house_number::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_postal_code::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_country, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(custom_values_text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(groups_text, '')), 'B')
|
|
WHERE id = member_id_val;
|
|
|
|
RETURN COALESCE(NEW, OLD);
|
|
END
|
|
$$ LANGUAGE plpgsql;
|
|
""")
|
|
|
|
# 3. Member groups trigger: include country when refreshing search_vector
|
|
execute("""
|
|
CREATE OR REPLACE FUNCTION update_member_search_vector_from_member_groups() RETURNS trigger AS $$
|
|
DECLARE
|
|
member_id_val uuid;
|
|
member_first_name text;
|
|
member_last_name text;
|
|
member_email text;
|
|
member_join_date date;
|
|
member_exit_date date;
|
|
member_notes text;
|
|
member_city text;
|
|
member_street text;
|
|
member_house_number text;
|
|
member_postal_code text;
|
|
member_country text;
|
|
custom_values_text text;
|
|
groups_text text;
|
|
BEGIN
|
|
FOR member_id_val IN
|
|
SELECT COALESCE(NEW.member_id, OLD.member_id)
|
|
UNION ALL
|
|
SELECT OLD.member_id
|
|
WHERE TG_OP = 'UPDATE' AND OLD.member_id IS DISTINCT FROM NEW.member_id
|
|
LOOP
|
|
SELECT
|
|
first_name,
|
|
last_name,
|
|
email,
|
|
join_date,
|
|
exit_date,
|
|
notes,
|
|
city,
|
|
street,
|
|
house_number,
|
|
postal_code,
|
|
country
|
|
INTO
|
|
member_first_name,
|
|
member_last_name,
|
|
member_email,
|
|
member_join_date,
|
|
member_exit_date,
|
|
member_notes,
|
|
member_city,
|
|
member_street,
|
|
member_house_number,
|
|
member_postal_code,
|
|
member_country
|
|
FROM members
|
|
WHERE id = member_id_val;
|
|
|
|
SELECT string_agg(
|
|
CASE
|
|
WHEN value ? '_union_value' THEN value->>'_union_value'
|
|
WHEN value ? 'value' THEN value->>'value'
|
|
ELSE ''
|
|
END,
|
|
' '
|
|
)
|
|
INTO custom_values_text
|
|
FROM custom_field_values
|
|
WHERE member_id = member_id_val AND value IS NOT NULL;
|
|
|
|
SELECT string_agg(g.name, ' ')
|
|
INTO groups_text
|
|
FROM member_groups mg
|
|
JOIN groups g ON g.id = mg.group_id
|
|
WHERE mg.member_id = member_id_val;
|
|
|
|
UPDATE members
|
|
SET search_vector =
|
|
setweight(to_tsvector('simple', coalesce(member_first_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(member_last_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(member_email, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(member_join_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(member_exit_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(member_notes, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(member_city, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_street, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_house_number::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_postal_code::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_country, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(custom_values_text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(groups_text, '')), 'B')
|
|
WHERE id = member_id_val;
|
|
END LOOP;
|
|
|
|
RETURN COALESCE(NEW, OLD);
|
|
END
|
|
$$ LANGUAGE plpgsql;
|
|
""")
|
|
|
|
# 4. Backfill: update all members' search_vector to include country
|
|
execute("""
|
|
UPDATE members m
|
|
SET search_vector =
|
|
setweight(to_tsvector('simple', coalesce(m.first_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(m.last_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(m.email, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(m.join_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(m.exit_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(m.notes, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(m.city, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(m.street, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(m.house_number::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(m.postal_code::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(m.country, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(
|
|
(SELECT string_agg(
|
|
CASE
|
|
WHEN value ? '_union_value' THEN value->>'_union_value'
|
|
WHEN value ? 'value' THEN value->>'value'
|
|
ELSE ''
|
|
END,
|
|
' '
|
|
)
|
|
FROM custom_field_values
|
|
WHERE member_id = m.id AND value IS NOT NULL),
|
|
''
|
|
)), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(
|
|
(SELECT string_agg(g.name, ' ')
|
|
FROM member_groups mg
|
|
JOIN groups g ON g.id = mg.group_id
|
|
WHERE mg.member_id = m.id),
|
|
''
|
|
)), 'B')
|
|
""")
|
|
end
|
|
|
|
def down do
|
|
# Restore trigger functions without country (revert to previous version from AddGroupNamesToMemberSearchVector)
|
|
execute("""
|
|
CREATE OR REPLACE FUNCTION members_search_vector_trigger() RETURNS trigger AS $$
|
|
DECLARE
|
|
custom_values_text text;
|
|
groups_text text;
|
|
BEGIN
|
|
SELECT string_agg(
|
|
CASE
|
|
WHEN value ? '_union_value' THEN value->>'_union_value'
|
|
WHEN value ? 'value' THEN value->>'value'
|
|
ELSE ''
|
|
END,
|
|
' '
|
|
)
|
|
INTO custom_values_text
|
|
FROM custom_field_values
|
|
WHERE member_id = NEW.id AND value IS NOT NULL;
|
|
|
|
SELECT string_agg(g.name, ' ')
|
|
INTO groups_text
|
|
FROM member_groups mg
|
|
JOIN groups g ON g.id = mg.group_id
|
|
WHERE mg.member_id = NEW.id;
|
|
|
|
NEW.search_vector :=
|
|
setweight(to_tsvector('simple', coalesce(NEW.first_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.last_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.email, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.join_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.exit_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.notes, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.city, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.street, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.house_number::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(NEW.postal_code::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(custom_values_text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(groups_text, '')), 'B');
|
|
RETURN NEW;
|
|
END
|
|
$$ LANGUAGE plpgsql;
|
|
""")
|
|
|
|
execute("""
|
|
CREATE OR REPLACE FUNCTION update_member_search_vector_from_custom_field_value() RETURNS trigger AS $$
|
|
DECLARE
|
|
member_id_val uuid;
|
|
member_first_name text;
|
|
member_last_name text;
|
|
member_email text;
|
|
member_join_date date;
|
|
member_exit_date date;
|
|
member_notes text;
|
|
member_city text;
|
|
member_street text;
|
|
member_house_number text;
|
|
member_postal_code text;
|
|
custom_values_text text;
|
|
groups_text text;
|
|
old_value_text text;
|
|
new_value_text text;
|
|
BEGIN
|
|
member_id_val := COALESCE(NEW.member_id, OLD.member_id);
|
|
|
|
IF TG_OP = 'UPDATE' THEN
|
|
old_value_text := COALESCE(
|
|
NULLIF(OLD.value->>'_union_value', ''),
|
|
NULLIF(OLD.value->>'value', ''),
|
|
''
|
|
);
|
|
new_value_text := COALESCE(
|
|
NULLIF(NEW.value->>'_union_value', ''),
|
|
NULLIF(NEW.value->>'value', ''),
|
|
''
|
|
);
|
|
IF (old_value_text IS NOT DISTINCT FROM new_value_text) AND
|
|
(OLD.member_id IS NOT DISTINCT FROM NEW.member_id) AND
|
|
(OLD.custom_field_id IS NOT DISTINCT FROM NEW.custom_field_id) THEN
|
|
RETURN COALESCE(NEW, OLD);
|
|
END IF;
|
|
END IF;
|
|
|
|
SELECT
|
|
first_name,
|
|
last_name,
|
|
email,
|
|
join_date,
|
|
exit_date,
|
|
notes,
|
|
city,
|
|
street,
|
|
house_number,
|
|
postal_code
|
|
INTO
|
|
member_first_name,
|
|
member_last_name,
|
|
member_email,
|
|
member_join_date,
|
|
member_exit_date,
|
|
member_notes,
|
|
member_city,
|
|
member_street,
|
|
member_house_number,
|
|
member_postal_code
|
|
FROM members
|
|
WHERE id = member_id_val;
|
|
|
|
SELECT string_agg(
|
|
CASE
|
|
WHEN value ? '_union_value' THEN value->>'_union_value'
|
|
WHEN value ? 'value' THEN value->>'value'
|
|
ELSE ''
|
|
END,
|
|
' '
|
|
)
|
|
INTO custom_values_text
|
|
FROM custom_field_values
|
|
WHERE member_id = member_id_val AND value IS NOT NULL;
|
|
|
|
SELECT string_agg(g.name, ' ')
|
|
INTO groups_text
|
|
FROM member_groups mg
|
|
JOIN groups g ON g.id = mg.group_id
|
|
WHERE mg.member_id = member_id_val;
|
|
|
|
UPDATE members
|
|
SET search_vector =
|
|
setweight(to_tsvector('simple', coalesce(member_first_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(member_last_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(member_email, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(member_join_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(member_exit_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(member_notes, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(member_city, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_street, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_house_number::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_postal_code::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(custom_values_text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(groups_text, '')), 'B')
|
|
WHERE id = member_id_val;
|
|
|
|
RETURN COALESCE(NEW, OLD);
|
|
END
|
|
$$ LANGUAGE plpgsql;
|
|
""")
|
|
|
|
execute("""
|
|
CREATE OR REPLACE FUNCTION update_member_search_vector_from_member_groups() RETURNS trigger AS $$
|
|
DECLARE
|
|
member_id_val uuid;
|
|
member_first_name text;
|
|
member_last_name text;
|
|
member_email text;
|
|
member_join_date date;
|
|
member_exit_date date;
|
|
member_notes text;
|
|
member_city text;
|
|
member_street text;
|
|
member_house_number text;
|
|
member_postal_code text;
|
|
custom_values_text text;
|
|
groups_text text;
|
|
BEGIN
|
|
FOR member_id_val IN
|
|
SELECT COALESCE(NEW.member_id, OLD.member_id)
|
|
UNION ALL
|
|
SELECT OLD.member_id
|
|
WHERE TG_OP = 'UPDATE' AND OLD.member_id IS DISTINCT FROM NEW.member_id
|
|
LOOP
|
|
SELECT
|
|
first_name,
|
|
last_name,
|
|
email,
|
|
join_date,
|
|
exit_date,
|
|
notes,
|
|
city,
|
|
street,
|
|
house_number,
|
|
postal_code
|
|
INTO
|
|
member_first_name,
|
|
member_last_name,
|
|
member_email,
|
|
member_join_date,
|
|
member_exit_date,
|
|
member_notes,
|
|
member_city,
|
|
member_street,
|
|
member_house_number,
|
|
member_postal_code
|
|
FROM members
|
|
WHERE id = member_id_val;
|
|
|
|
SELECT string_agg(
|
|
CASE
|
|
WHEN value ? '_union_value' THEN value->>'_union_value'
|
|
WHEN value ? 'value' THEN value->>'value'
|
|
ELSE ''
|
|
END,
|
|
' '
|
|
)
|
|
INTO custom_values_text
|
|
FROM custom_field_values
|
|
WHERE member_id = member_id_val AND value IS NOT NULL;
|
|
|
|
SELECT string_agg(g.name, ' ')
|
|
INTO groups_text
|
|
FROM member_groups mg
|
|
JOIN groups g ON g.id = mg.group_id
|
|
WHERE mg.member_id = member_id_val;
|
|
|
|
UPDATE members
|
|
SET search_vector =
|
|
setweight(to_tsvector('simple', coalesce(member_first_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(member_last_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(member_email, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(member_join_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(member_exit_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(member_notes, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(member_city, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_street, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_house_number::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(member_postal_code::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(custom_values_text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(groups_text, '')), 'B')
|
|
WHERE id = member_id_val;
|
|
END LOOP;
|
|
|
|
RETURN COALESCE(NEW, OLD);
|
|
END
|
|
$$ LANGUAGE plpgsql;
|
|
""")
|
|
|
|
# Backfill without country
|
|
execute("""
|
|
UPDATE members m
|
|
SET search_vector =
|
|
setweight(to_tsvector('simple', coalesce(m.first_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(m.last_name, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(m.email, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(m.join_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(m.exit_date::text, '')), 'D') ||
|
|
setweight(to_tsvector('simple', coalesce(m.notes, '')), 'B') ||
|
|
setweight(to_tsvector('simple', coalesce(m.city, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(m.street, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(m.house_number::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(m.postal_code::text, '')), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(
|
|
(SELECT string_agg(
|
|
CASE
|
|
WHEN value ? '_union_value' THEN value->>'_union_value'
|
|
WHEN value ? 'value' THEN value->>'value'
|
|
ELSE ''
|
|
END,
|
|
' '
|
|
)
|
|
FROM custom_field_values
|
|
WHERE member_id = m.id AND value IS NOT NULL),
|
|
''
|
|
)), 'C') ||
|
|
setweight(to_tsvector('simple', coalesce(
|
|
(SELECT string_agg(g.name, ' ')
|
|
FROM member_groups mg
|
|
JOIN groups g ON g.id = mg.group_id
|
|
WHERE mg.member_id = m.id),
|
|
''
|
|
)), 'B')
|
|
""")
|
|
|
|
alter table(:members) do
|
|
remove :country
|
|
end
|
|
end
|
|
end
|