36 lines
1.6 KiB
SQL
36 lines
1.6 KiB
SQL
|
-- 16_fix_inet_to_text.sql
|
||
|
|
-- The C# domain models all IP/network columns as string / string[]. The original
|
||
|
|
-- schema declared them as native PostgreSQL INET / INET[], which fails at runtime
|
||
|
|
-- with: "column ... is of type inet but expression is of type text".
|
||
|
|
--
|
||
|
|
-- Rather than add per-property EF value converters across every service, we align
|
||
|
|
-- the schema with the (string-based) code: convert every inet/inet[] column to
|
||
|
|
-- text/text[]. This block is idempotent — it only touches columns still typed inet,
|
||
|
|
-- and alters partitioned parents (which cascade) while skipping partition children.
|
||
|
|
|
||
|
|
DO $$
|
||
|
|
DECLARE r record;
|
||
|
|
BEGIN
|
||
|
|
FOR r IN
|
||
|
|
SELECT n.nspname AS sch, c.relname AS tbl, a.attname AS col, t.typname AS typ
|
||
|
|
FROM pg_attribute a
|
||
|
|
JOIN pg_class c ON c.oid = a.attrelid
|
||
|
|
JOIN pg_namespace n ON n.oid = c.relnamespace
|
||
|
|
JOIN pg_type t ON t.oid = a.atttypid
|
||
|
|
WHERE n.nspname IN ('identity','content','studio','render','notification','file_mgr')
|
||
|
|
AND a.attnum > 0 AND NOT a.attisdropped
|
||
|
|
AND c.relkind IN ('r','p') -- ordinary + partitioned parents
|
||
|
|
AND NOT c.relispartition -- skip partition children (parent cascades)
|
||
|
|
AND t.typname IN ('inet','_inet')
|
||
|
|
LOOP
|
||
|
|
IF r.typ = '_inet' THEN
|
||
|
|
EXECUTE format('ALTER TABLE %I.%I ALTER COLUMN %I TYPE text[] USING %I::text[];',
|
||
|
|
r.sch, r.tbl, r.col, r.col);
|
||
|
|
ELSE
|
||
|
|
EXECUTE format('ALTER TABLE %I.%I ALTER COLUMN %I TYPE text USING %I::text;',
|
||
|
|
r.sch, r.tbl, r.col, r.col);
|
||
|
|
END IF;
|
||
|
|
RAISE NOTICE 'inet->text: %.%.% (%)', r.sch, r.tbl, r.col, r.typ;
|
||
|
|
END LOOP;
|
||
|
|
END $$;
|