| -- |
| -- This function dumps the catalog DDL into a table called catdump.DDL |
| -- |
| drop schema catdump cascade; |
| ERROR: schema "catdump" does not exist |
| create schema catdump; |
| create table catdump.DDL(ver text, stmt text); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ver' as the Greenplum Database data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| create or replace function catdump.dump_to_table(ver text, src_schema name) returns int as $$ |
| declare |
| stmtrow record; |
| begin |
| -- Dump Access Methods |
| for stmtrow in |
| SELECT |
| 'CREATE FUNCTION upg_catalog.' || quote_ident(proname) |
| || '(' |
| || coalesce( |
| array_to_string( array( |
| select coalesce(proargnames[i] || ' ','') |
| || case when typname = 'internal' then 'pg_catalog.' else 'upg_catalog.' end |
| || quote_ident(typname) |
| from pg_type t, generate_series(1, pronargs) i |
| where t.oid = proargtypes[i-1] |
| order by i), ', '), '') |
| || ') RETURNS ' |
| || case when proretset then 'SETOF ' else '' end |
| || 'pg_catalog.' || quote_ident(r.typname) || ' ' |
| || 'LANGUAGE ' || quote_ident(lanname) || ' ' |
| || case when provolatile = 'i' then 'IMMUTABLE ' |
| when provolatile = 'v' then 'VOLATILE ' |
| when provolatile = 's' then 'STABLE ' |
| else '' end |
| || case when proisstrict then 'STRICT ' else '' end |
| || case when prosecdef then 'SECURITY DEFINER ' else '' end |
| || 'AS ' |
| || case when lanname = 'c' |
| then '''' || textin(byteaout(probin)) || ''', ''' || prosrc || '''' |
| when lanname = 'internal' |
| then '''' || prosrc || '''' |
| when lanname = 'sql' |
| then E'\$\$' || prosrc || E'\$\$' |
| else 'BROKEN (unsupported language)' end |
| || ';' as stmt |
| FROM pg_proc p |
| JOIN pg_type r on (p.prorettype = r.oid) |
| JOIN pg_language l on (p.prolang = l.oid) |
| JOIN pg_namespace n on (p.pronamespace = n.oid) |
| WHERE n.nspname = src_schema |
| and p.oid in (select amproc from pg_amproc) |
| order by pronargs, 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| end loop; |
| |
| -- Aggregate |
| for stmtrow in |
| SELECT 'CREATE AGGREGATE upg_catalog.' || quote_ident(p.proname) || '(' |
| || coalesce(array_to_string(array( |
| select 'upg_catalog.' || quote_ident(typname) |
| from pg_type t, generate_series(1, p.pronargs) i |
| where t.oid = p.proargtypes[i-1] |
| order by i), ', '), '') |
| || ') (' |
| || E' SFUNC = ' || quote_ident(sfunc.proname) |
| || E', STYPE = upg_catalog.' || quote_ident(stype.typname) |
| || case when prefunc.oid is not null then E', PREFUNC = ' || quote_ident(prefunc.proname) else '' end |
| || case when finfunc.oid is not null then E', FINALFUNC = ' || quote_ident(finfunc.proname) else '' end |
| || case when agginitval is not null then E', INITCOND = ''' || agginitval || '''' else '' end |
| || case when sortop.oid is not null then E', SORTOP = ' || quote_ident(sortop.oprname) else '' end |
| || E' );' as stmt |
| FROM pg_aggregate a |
| join pg_proc p on (a.aggfnoid = p.oid) |
| join pg_namespace n on (p.pronamespace = n.oid) |
| join pg_proc sfunc on (a.aggtransfn = sfunc.oid) |
| join pg_type stype on (a.aggtranstype = stype.oid) |
| left join pg_proc prefunc on (a.aggprelimfn = prefunc.oid) |
| left join pg_proc finfunc on (a.aggfinalfn = finfunc.oid) |
| left join pg_operator sortop on (a.aggsortop = sortop.oid) |
| WHERE n.nspname = src_schema |
| ORDER BY 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| end loop; |
| |
| |
| -- |
| -- Cast Functions |
| -- |
| for stmtrow in |
| SELECT |
| 'CREATE FUNCTION upg_catalog.' || quote_ident(proname) |
| || '(' |
| || coalesce( |
| array_to_string( array( |
| select coalesce(proargnames[i] || ' ','') |
| || case when i > 1 |
| then 'pg_catalog.' |
| else 'upg_catalog.' end |
| || quote_ident(typname) |
| from pg_type t, generate_series(1, pronargs) i |
| where t.oid = proargtypes[i-1] |
| order by i), ', '), '') |
| || ') RETURNS ' |
| || case when proretset then 'SETOF ' else '' end |
| || 'upg_catalog.' || quote_ident(r.typname) || ' ' |
| || 'LANGUAGE ' || quote_ident(lanname) || ' ' |
| || case when provolatile = 'i' then 'IMMUTABLE ' |
| when provolatile = 'v' then 'VOLATILE ' |
| when provolatile = 's' then 'STABLE ' |
| else '' end |
| || case when proisstrict then 'STRICT ' else '' end |
| || case when prosecdef then 'SECURITY DEFINER ' else '' end |
| || 'AS ' |
| || case when lanname = 'c' |
| then '''' || textin(byteaout(probin)) || ''', ''' || prosrc || '''' |
| when lanname = 'internal' |
| then '''' || prosrc || '''' |
| when lanname = 'sql' |
| then E'\$\$ SELECT null::upg_catalog.' || quote_ident(r.typname) || E'\$\$' |
| else 'BROKEN (unsupported language)' end |
| || ';' as stmt |
| FROM pg_proc p |
| JOIN pg_type r on (p.prorettype = r.oid) |
| JOIN pg_language l on (p.prolang = l.oid) |
| JOIN pg_namespace n on (p.pronamespace = n.oid) |
| WHERE n.nspname = src_schema |
| and p.oid in (select castfunc from pg_cast) |
| order by 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| |
| -- |
| -- Casts |
| -- |
| for stmtrow in |
| SELECT 'CREATE CAST (' |
| || 'upg_catalog.' || quote_ident(t1.typname) || ' AS ' |
| || 'upg_catalog.' || quote_ident(t2.typname) || ') ' |
| || case when proname is not null |
| then 'WITH FUNCTION ' |
| || case when p.proargtypes[0] != c.castsource or p.prorettype != c.casttarget |
| then 'dummy_cast_functions.dummycast_' |
| || t1.typname || '_' || t2.typname || '_' || p.proname |
| || '(upg_catalog.' || quote_ident(t1.typname) || ')' |
| else 'upg_catalog.' || p.proname || '(' |
| || array_to_string( array ( |
| select coalesce(proargnames[i] || ' ','') |
| || case when i = 1 then 'upg_catalog.' else 'pg_catalog.' end |
| || quote_ident(typname) |
| from pg_type t, generate_series(1, pronargs) i |
| where t.oid = proargtypes[i-1] |
| order by i), ', ') || ')' |
| end |
| else 'WITHOUT FUNCTION' end |
| || case when castcontext = 'e' then '' |
| when castcontext = 'i' then ' AS IMPLICIT' |
| when castcontext = 'a' then ' AS ASSIGNMENT' end |
| || ';' as stmt |
| FROM pg_cast c |
| join pg_type t1 on (c.castsource = t1.oid) |
| join pg_type t2 on (c.casttarget = t2.oid) |
| left join pg_proc p on (c.castfunc = p.oid) |
| ORDER BY 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| |
| |
| -- |
| -- Dummy Cast |
| -- |
| for stmtrow in |
| SELECT 'CREATE FUNCTION ' |
| || 'dummy_cast_functions.dummycast_'||t1.typname||'_'||t2.typname||'_'||p.proname||'(' |
| || 'upg_catalog.' || quote_ident(t1.typname) || ') ' |
| || 'RETURNS upg_catalog.' || quote_ident(t2.typname) || ' ' |
| || 'LANGUAGE internal AS ''' || p.prosrc || ''' STRICT IMMUTABLE;' as stmt |
| FROM pg_cast c |
| join pg_type t1 on (c.castsource = t1.oid) |
| join pg_type t2 on (c.casttarget = t2.oid) |
| join pg_proc p on (c.castfunc = p.oid) |
| join pg_namespace n on (p.pronamespace = n.oid) |
| WHERE n.nspname = 'pg_catalog' |
| AND p.proargtypes[0] != c.castsource OR p.prorettype != c.casttarget |
| ORDER BY 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| |
| -- |
| -- Functions SQL |
| -- |
| for stmtrow in |
| SELECT |
| 'CREATE FUNCTION upg_catalog.' |
| || case when proname in ('convert') |
| then '"' || proname || '"' |
| else quote_ident(proname) end |
| || '(' |
| || coalesce( |
| array_to_string( |
| case when proargmodes is null |
| then array( |
| select coalesce(proargnames[i] || ' ','') |
| || 'upg_catalog.' || quote_ident(typname) |
| from pg_type t, generate_series(1, pronargs) i |
| where t.oid = proargtypes[i-1] |
| order by i) |
| else array( |
| select case when proargmodes[i] = 'i' then 'IN ' |
| when proargmodes[i] = 'o' then 'OUT ' |
| when proargmodes[i] = 'b' then 'INOUT ' |
| else 'BROKEN(proargmode)' end |
| || coalesce(proargnames[i] || ' ','') |
| || 'upg_catalog.' || quote_ident(typname) |
| from pg_type t, generate_series(1, array_upper(proargmodes, 1)) i |
| where t.oid = proallargtypes[i] |
| order by i) |
| end, ', '), '') |
| || ') RETURNS ' |
| || case when proretset then 'SETOF ' else '' end |
| || 'upg_catalog.' || quote_ident(r.typname) || ' ' |
| || 'LANGUAGE ' || quote_ident(lanname) || ' ' |
| || case when provolatile = 'i' then 'IMMUTABLE ' |
| when provolatile = 'v' then 'VOLATILE ' |
| when provolatile = 's' then 'STABLE ' |
| else '' end |
| || case when proisstrict then 'STRICT ' else '' end |
| || case when prosecdef then 'SECURITY DEFINER ' else '' end |
| || 'AS ''select null::upg_catalog.' || quote_ident(r.typname) || ''';' as stmt |
| FROM pg_proc p |
| JOIN pg_type r on (p.prorettype = r.oid) |
| JOIN pg_language l on (p.prolang = l.oid) |
| JOIN pg_namespace n on (p.pronamespace = n.oid) |
| WHERE n.nspname = src_schema |
| and proisagg = 'f' |
| and proiswin = 'f' |
| and p.oid not in (select unnest(array[typinput, typoutput, typreceive, typsend, typanalyze]) |
| from pg_type) |
| and p.oid not in (select castfunc from pg_cast) |
| and p.oid not in (select conproc from pg_conversion) |
| and p.oid not in (select unnest(array[oprrest, oprjoin]) from pg_operator) |
| and p.oid not in (select unnest(array['pg_catalog.shell_in'::regproc, 'pg_catalog.shell_out'::regproc])) |
| and lanname = 'sql' |
| order by 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| |
| -- |
| -- Functions |
| -- |
| for stmtrow in |
| SELECT |
| 'CREATE FUNCTION upg_catalog.' |
| || case when proname in ('convert') then '"' || proname || '"' else quote_ident(proname) end |
| || '(' |
| || coalesce( |
| array_to_string( |
| case when proargmodes is null |
| then array( |
| select coalesce(proargnames[i] || ' ','') |
| || 'upg_catalog.' || quote_ident(typname) |
| from pg_type t, generate_series(1, pronargs) i |
| where t.oid = proargtypes[i-1] |
| order by i) |
| else array( |
| select case when proargmodes[i] = 'i' then 'IN ' |
| when proargmodes[i] = 'o' then 'OUT ' |
| when proargmodes[i] = 'b' then 'INOUT ' |
| else 'BROKEN(proargmode)' end |
| || coalesce(proargnames[i] || ' ','') |
| || 'upg_catalog.' || quote_ident(typname) |
| from pg_type t, generate_series(1, array_upper(proargmodes, 1)) i |
| where t.oid = proallargtypes[i] |
| order by i) |
| end, ', '), '') |
| || ') RETURNS ' |
| || case when proretset then 'SETOF ' else '' end |
| || case when lanname = 'sql' or proargmodes is not null then 'pg_catalog.' else 'upg_catalog.' end |
| || quote_ident(r.typname) || ' ' |
| || 'LANGUAGE ' || quote_ident(lanname) || ' ' |
| || case when provolatile = 'i' then 'IMMUTABLE ' |
| when provolatile = 'v' then 'VOLATILE ' |
| when provolatile = 's' then 'STABLE ' |
| else '' end |
| || case when proisstrict then 'STRICT ' else '' end |
| || case when prosecdef then 'SECURITY DEFINER ' else '' end |
| || 'AS ' |
| || case when lanname = 'c' |
| then '''' || textin(byteaout(probin)) || ''', ''' || prosrc || '''' |
| when lanname = 'internal' |
| then '''' || prosrc || '''' |
| else 'BROKEN (unsupported language)' end |
| || ';' as stmt |
| FROM pg_proc p |
| JOIN pg_type r on (p.prorettype = r.oid) |
| JOIN pg_language l on (p.prolang = l.oid) |
| JOIN pg_namespace n on (p.pronamespace = n.oid) |
| WHERE n.nspname = src_schema |
| and proisagg = 'f' |
| and proiswin = 'f' |
| and p.oid not in (select unnest(array[typinput, typoutput, typreceive, typsend, typanalyze]) from pg_type) |
| and p.oid not in (select castfunc from pg_cast) |
| and p.oid not in (select conproc from pg_conversion) |
| and p.oid not in (select unnest(array[oprrest, oprjoin]) from pg_operator) |
| and p.oid not in (select oprcode from pg_operator where oprname in ('<', '<=', '=', '>', '>=')) |
| and p.oid not in (select oprcode from pg_operator where oid in (select amopopr from pg_amop)) |
| and p.oid not in (select amproc from pg_amproc) |
| and p.oid not in (select unnest(array['pg_catalog.shell_in'::regproc, 'pg_catalog.shell_out'::regproc])) |
| and p.oid not in (select unnest(array[lanplcallfoid, lanvalidator]) |
| from pg_language where lanname not in ('internal', 'c', 'sql', 'plpgsql')) |
| and l.lanname != 'sql' |
| order by 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| |
| |
| -- |
| -- Indexes |
| -- |
| for stmtrow in |
| SELECT |
| CASE when indisprimary then -- (primary key index) |
| 'BROKEN(primary key)' -- (none in catalog, this would be an ALTER TABLE) |
| when indisunique then -- (unique index) |
| 'CREATE UNIQUE INDEX ' |
| else -- (normal index) |
| 'CREATE INDEX ' |
| end |
| || quote_ident(c.relname) || ' ON ' |
| || 'upg_catalog.' || quote_ident(rel.relname) || ' ' |
| || 'USING ' || am.amname || '(' |
| || array_to_string(array( |
| select attname |
| from pg_attribute, generate_series(array_lower(indkey,1), array_upper(indkey,1)) i |
| where attrelid=indrelid and attnum = indkey[i] |
| order by i), ', ') |
| || ');' as stmt |
| FROM pg_index |
| join pg_class c ON (indexrelid = c.oid) |
| join pg_class rel ON (indrelid = rel.oid) |
| join pg_namespace n ON (c.relnamespace = n.oid) |
| join pg_am am ON (c.relam = am.oid) |
| WHERE n.nspname = src_schema |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| |
| -- |
| -- Operator classes |
| -- |
| for stmtrow in |
| SELECT 'CREATE OPERATOR CLASS ' || quote_ident(opcname) || ' ' |
| || case when t.typname = 'varchar' then ' FOR TYPE upg_catalog.text ' |
| when t.typname = 'cidr' then ' FOR TYPE upg_catalog.inet ' |
| else case when opcdefault then 'DEFAULT ' else '' end |
| || 'FOR TYPE upg_catalog.' || quote_ident(t.typname) || ' ' |
| end |
| || 'USING ' || amname || ' ' |
| || E'AS ' |
| || array_to_string(array( |
| SELECT ' '||kind||' '||strategy||' '||name||'('||param||')'||option |
| FROM ( |
| SELECT amopclaid as classid, |
| amopsubtype as subtype, |
| 0 as sort, |
| amopstrategy as strategy, |
| 'OPERATOR' as kind, |
| 'upg_catalog.' || op.oprname as name, |
| 'upg_catalog.' || t1.typname |
| || ', upg_catalog.' |
| || t2.typname as param, |
| case when amopreqcheck then ' RECHECK' else '' end as option |
| FROM pg_amop amop |
| join pg_operator op on (amopopr = op.oid) |
| left join pg_type t1 on (op.oprleft = t1.oid) |
| left join pg_type t2 on (op.oprright = t2.oid) |
| UNION ALL |
| SELECT amopclaid as classid, |
| amprocsubtype as subtype, |
| 1 as sort, |
| amprocnum as opstrategy, |
| 'FUNCTION' as opkind, |
| 'upg_catalog.' || quote_ident(p.proname) as name, |
| coalesce(array_to_string(array( |
| SELECT case when typname = 'internal' |
| then 'pg_catalog.' |
| else 'upg_catalog.' end || quote_ident(typname) |
| FROM pg_type t, generate_series(1, pronargs) i |
| WHERE t.oid = proargtypes[i-1] |
| ORDER BY i), ', '), '') as param, |
| '' as option |
| FROM pg_amproc amproc |
| join pg_proc p on (amproc.amproc = p.oid) |
| ) q |
| WHERE classid = opc.oid -- correlate to outer query block |
| ORDER BY subtype, sort, strategy), E', ') |
| || coalesce(E', STORAGE ' || quote_ident(keytype.typname), '') || ';' as stmt |
| FROM pg_opclass opc |
| join pg_type t on (opc.opcintype = t.oid) |
| join pg_am am on (opc.opcamid = am.oid) |
| join pg_namespace n on (opc.opcnamespace = n.oid) |
| left join pg_type keytype on (opc.opckeytype = keytype.oid) |
| WHERE n.nspname = src_schema |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| |
| -- |
| -- Operator Comparison |
| -- |
| for stmtrow in |
| SELECT |
| 'CREATE FUNCTION upg_catalog.' || quote_ident(proname) |
| || '(' |
| || coalesce( |
| array_to_string( array( |
| select coalesce(proargnames[i] || ' ','') |
| || 'upg_catalog.' || quote_ident(typname) |
| from pg_type t, generate_series(1, pronargs) i |
| where t.oid = proargtypes[i-1] |
| order by i), ', '), '') |
| || ') RETURNS ' |
| || case when proretset then 'SETOF ' else '' end |
| || 'pg_catalog.' || quote_ident(r.typname) || ' ' |
| || 'LANGUAGE ' || quote_ident(lanname) || ' ' |
| || case when provolatile = 'i' then 'IMMUTABLE ' |
| when provolatile = 'v' then 'VOLATILE ' |
| when provolatile = 's' then 'STABLE ' |
| else '' end |
| || case when proisstrict then 'STRICT ' else '' end |
| || case when prosecdef then 'SECURITY DEFINER ' else '' end |
| || 'AS ' |
| || case when lanname = 'c' |
| then '''' || textin(byteaout(probin)) || ''', ''' || prosrc || '''' |
| when lanname = 'internal' |
| then '''' || prosrc || '''' |
| when lanname = 'sql' |
| then E'\$\$' || prosrc || E'\$\$' |
| else 'BROKEN (unsupported language)' end |
| || ';' as stmt |
| FROM pg_proc p |
| JOIN pg_type r on (p.prorettype = r.oid) |
| JOIN pg_language l on (p.prolang = l.oid) |
| JOIN pg_namespace n on (p.pronamespace = n.oid) |
| WHERE n.nspname = src_schema |
| and p.oid in (select oprcode from pg_operator |
| where oprname in ('<', '<=', '=', '>', '>=') |
| or oid in (select amopopr from pg_amop)) |
| order by 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| |
| -- |
| -- Operator Functions |
| -- |
| for stmtrow in |
| SELECT |
| 'CREATE FUNCTION upg_catalog.' || quote_ident(proname) |
| || '(' |
| || coalesce( |
| array_to_string( array( |
| select coalesce(proargnames[i] || ' ','') |
| || 'pg_catalog.' || quote_ident(typname) |
| from pg_type t, generate_series(1, pronargs) i |
| where t.oid = proargtypes[i-1] |
| order by i), ', '), '') |
| || ') RETURNS ' |
| || case when proretset then 'SETOF ' else '' end |
| || 'pg_catalog.' || quote_ident(r.typname) || ' ' |
| || 'LANGUAGE ' || quote_ident(lanname) || ' ' |
| || case when provolatile = 'i' then 'IMMUTABLE ' |
| when provolatile = 'v' then 'VOLATILE ' |
| when provolatile = 's' then 'STABLE ' |
| else '' end |
| || case when proisstrict then 'STRICT ' else '' end |
| || case when prosecdef then 'SECURITY DEFINER ' else '' end |
| || 'AS ' |
| || case when lanname = 'c' |
| then '''' || textin(byteaout(probin)) || ''', ''' || prosrc || '''' |
| when lanname = 'internal' |
| then '''' || prosrc || '''' |
| when lanname = 'sql' |
| then E'\$\$' || prosrc || E'\$\$' |
| else 'BROKEN (unsupported language)' end |
| || ';' as stmt |
| FROM pg_proc p |
| JOIN pg_type r on (p.prorettype = r.oid) |
| JOIN pg_language l on (p.prolang = l.oid) |
| JOIN pg_namespace n on (p.pronamespace = n.oid) |
| WHERE n.nspname = src_schema |
| and p.oid in (select unnest(array[oprrest, oprjoin]) from pg_operator) |
| order by 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| |
| -- |
| -- Operator Selectivity |
| -- |
| for stmtrow in |
| SELECT |
| 'CREATE OR REPLACE OR REPLACE FUNCTION upg_catalog.' || quote_ident(proname) |
| || '(' |
| || coalesce( |
| array_to_string( array( |
| select coalesce(proargnames[i] || ' ','') |
| || 'pg_catalog.' || quote_ident(typname) |
| from pg_type t, generate_series(1, pronargs) i |
| where t.oid = proargtypes[i-1] |
| order by i), ', '), '') |
| || ') RETURNS ' |
| || case when proretset then 'SETOF ' else '' end |
| || 'pg_catalog.' || quote_ident(r.typname) || ' ' |
| || 'LANGUAGE ' || quote_ident(lanname) || ' ' |
| || case when provolatile = 'i' then 'IMMUTABLE ' |
| when provolatile = 'v' then 'VOLATILE ' |
| when provolatile = 's' then 'STABLE ' |
| else '' end |
| || case when proisstrict then 'STRICT ' else '' end |
| || case when prosecdef then 'SECURITY DEFINER ' else '' end |
| || 'AS ' |
| || case when lanname = 'c' |
| then '''' || textin(byteaout(probin)) || ''', ''' || prosrc || '''' |
| when lanname = 'internal' |
| then '''' || prosrc || '''' |
| when lanname = 'sql' |
| then E'\$\$' || prosrc || E'\$\$' |
| else 'BROKEN (unsupported language)' end |
| || ';' as stmt |
| FROM pg_proc p |
| JOIN pg_type r on (p.prorettype = r.oid) |
| JOIN pg_language l on (p.prolang = l.oid) |
| JOIN pg_namespace n on (p.pronamespace = n.oid) |
| WHERE n.nspname = src_schema |
| and p.oid in (select unnest(array[oprrest, oprjoin]) from pg_operator) |
| order by 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| |
| -- |
| -- Operator |
| -- |
| for stmtrow in |
| SELECT 'CREATE OPERATOR upg_catalog.' || o.oprname || '(' |
| || E' PROCEDURE = upg_catalog.' || quote_ident(p.proname) |
| || case when tleft.typname is not null |
| then E', LEFTARG = upg_catalog.' || quote_ident(tleft.typname) else '' end |
| || case when tright.typname is not null |
| then E', RIGHTARG = upg_catalog.' || quote_ident(tright.typname) else '' end |
| || case when com.oprname is not null |
| then E', COMMUTATOR = ' || com.oprname else '' end |
| || case when neg.oprname is not null |
| then E', NEGATOR = ' || neg.oprname else '' end |
| || case when rest.proname is not null |
| then E', RESTRICT = upg_catalog.' || quote_ident(rest.proname) else '' end |
| || case when pjoin.proname is not null |
| then E', JOIN = upg_catalog.' || quote_ident(pjoin.proname) else '' end |
| || case when o.oprcanhash |
| then E', HASHES' else '' end |
| || case when sort1.oprname is not null |
| then E', SORT1 = ' || sort1.oprname else '' end |
| || case when sort2.oprname is not null |
| then E', SORT2 = ' || sort2.oprname else '' end |
| || case when ltcmp.oprname is not null |
| then E', LTCMP = ' || ltcmp.oprname else '' end |
| || case when gtcmp.oprname is not null |
| then E', GTCMP = ' || gtcmp.oprname else '' end |
| || E' );' as stmt |
| FROM pg_operator o |
| join pg_namespace n on (o.oprnamespace = n.oid) |
| join pg_proc p on (o.oprcode = p.oid) |
| left join pg_type tleft on (o.oprleft = tleft.oid) |
| left join pg_type tright on (o.oprright = tright.oid) |
| left join pg_operator com on (o.oprcom = com.oid and o.oid > com.oid) |
| left join pg_operator neg on (o.oprnegate = neg.oid and o.oid > neg.oid) |
| left join pg_proc rest on (o.oprrest = rest.oid) |
| left join pg_proc pjoin on (o.oprjoin = pjoin.oid) |
| left join pg_operator sort1 on (o.oprlsortop = sort1.oid) |
| left join pg_operator sort2 on (o.oprrsortop = sort2.oid) |
| left join pg_operator ltcmp on (o.oprltcmpop = ltcmp.oid) |
| left join pg_operator gtcmp on (o.oprgtcmpop = gtcmp.oid) |
| WHERE n.nspname = src_schema |
| ORDER BY 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| |
| |
| |
| -- |
| -- type functions |
| -- |
| for stmtrow in |
| SELECT |
| 'CREATE FUNCTION upg_catalog.' || quote_ident(proname) |
| || '(' |
| || coalesce( |
| array_to_string( array( |
| select coalesce(proargnames[i] || ' ','') |
| || case when p.oid in (select unnest(array[typoutput, typsend]) from pg_type) |
| then 'upg_catalog.' |
| else 'pg_catalog.' end |
| || quote_ident(typname) |
| from pg_type t, generate_series(1, pronargs) i |
| where t.oid = proargtypes[i-1] |
| order by i), ', '), |
| '') |
| || ') RETURNS ' |
| || case when proretset then 'SETOF ' else '' end |
| || case when p.oid in (select unnest(array[typinput, typreceive]) from pg_type) |
| then 'upg_catalog.' |
| else 'pg_catalog.' end |
| || quote_ident(r.typname) || ' ' |
| || 'LANGUAGE ' || quote_ident(lanname) || ' ' |
| || case when provolatile = 'i' then 'IMMUTABLE ' |
| when provolatile = 'v' then 'VOLATILE ' |
| when provolatile = 's' then 'STABLE ' |
| else '' end |
| || case when proisstrict then 'STRICT ' else '' end |
| || case when prosecdef then 'SECURITY DEFINER ' else '' end |
| || 'AS ' |
| || case when lanname = 'c' |
| then '''' || textin(byteaout(probin)) || ''', ''' || prosrc || '''' |
| when lanname = 'internal' |
| then '''' || prosrc || '''' |
| when lanname = 'sql' |
| then E'\$\$' || prosrc || E'\$\$' |
| else 'BROKEN (unsupported language)' end |
| || ';' as stmt |
| FROM pg_proc p |
| JOIN pg_type r on (p.prorettype = r.oid) |
| JOIN pg_language l on (p.prolang = l.oid) |
| JOIN pg_namespace n on (p.pronamespace = n.oid) |
| WHERE n.nspname = src_schema |
| and proisagg = 'f' |
| and proiswin = 'f' |
| and p.oid in (select unnest(array[typinput, typoutput, typreceive, typsend, typanalyze]) |
| from pg_type) |
| order by 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| |
| |
| -- |
| -- type shells |
| -- |
| for stmtrow in |
| SELECT 'CREATE TYPE upg_catalog.' || quote_ident(typname) || ';' as stmt |
| FROM pg_type t |
| JOIN pg_namespace n on (t.typnamespace = n.oid) |
| WHERE typtype in ('b', 'p') and typname !~ '^_' and n.nspname = src_schema |
| order by 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| |
| -- |
| -- type shells |
| -- |
| for stmtrow in |
| SELECT 'CREATE TYPE upg_catalog.' || quote_ident(t.typname) |
| || '(' |
| || E' INPUT = ' |
| || case when pin.prorettype = t.oid |
| then 'upg_catalog.' |
| else 'dummy_cast_functions.' |
| end || pin.proname |
| || E', OUTPUT = ' |
| || case when pout.proargtypes[0] = t.oid |
| then 'upg_catalog.' |
| else 'dummy_cast_functions.' |
| end || pout.proname |
| || case when precv.proname is not null |
| then E', RECEIVE = ' || |
| case when precv.prorettype = t.oid |
| then 'upg_catalog.' |
| else 'dummy_cast_functions.' |
| end || precv.proname |
| else '' end |
| || case when psend.proname is not null |
| then E', SEND = ' || |
| case when psend.proargtypes[0] = t.oid |
| then 'upg_catalog.' |
| else 'dummy_cast_functions.' |
| end || psend.proname |
| else '' end |
| || case when panalyze.proname is not null |
| then E', ANALYZE = upg_catalog.' || panalyze.proname |
| else '' end |
| || case when t.typlen = -1 |
| then E', INTERNALLENGTH = VARIABLE' |
| else E', INTERNALLENGTH = ' || t.typlen end |
| || case when t.typbyval |
| then E', PASSEDBYVALUE' else '' end |
| || E', STORAGE = ' |
| || case when t.typstorage = 'p' then 'plain' |
| when t.typstorage = 'x' then 'extended' |
| when t.typstorage = 'm' then 'main' |
| when t.typstorage = 'e' then 'external' |
| else 'BROKEN' end |
| || case when t.typdefault is not null |
| then E', DEFAULT = ' || t.typdefault else '' end |
| || case when telement.typname is not null |
| then E', ELEMENT = ' || telement.typname else '' end |
| || E', DELIMITER = ''' || t.typdelim || '''' |
| || E', ALIGNMENT = ' |
| || case when t.typalign = 'c' then 'char' |
| when t.typalign = 's' then 'int2' |
| when t.typalign = 'i' then 'int4' |
| when t.typalign = 'd' then 'double' |
| else 'BROKEN' end |
| || E' );' |
| || case when (t.typtype = 'p' or t.typname in ('smgr', 'unknown')) |
| then E' DROP TYPE upg_catalog._' || t.typname || ';' |
| else '' end as stmt |
| FROM pg_type t |
| join pg_namespace n on (t.typnamespace = n.oid) |
| join pg_proc pin on (t.typinput = pin.oid) |
| join pg_proc pout on (t.typoutput = pout.oid) |
| left join pg_proc precv on (t.typreceive = precv.oid) |
| left join pg_proc psend on (t.typsend = psend.oid) |
| left join pg_proc panalyze on (t.typanalyze = panalyze.oid) |
| left join pg_type telement on (t.typelem = telement.oid) |
| WHERE t.typtype in ('b', 'p') and t.typname !~ '^_' and n.nspname = src_schema |
| order by 1 |
| loop |
| insert into catdump.DDL values(ver, stmtrow.stmt); |
| |
| end loop; |
| |
| return 1; |
| end; |
| $$ language 'plpgsql'; |
| select catdump.dump_to_table('4.2', 'pg_catalog'); |
| dump_to_table |
| --------------- |
| 1 |
| (1 row) |
| |
| -- |
| -- Now, load the 4.1 catalog into catdump.ddl |
| -- |
| drop external table cat41; |
| ERROR: table "cat41" does not exist |
| create external table cat41(stmt text) location('file://@hostname@@abs_srcdir@/data/upgrade42/catalog41/4_1_dump.txt') |
| format 'TEXT' (delimiter 'OFF'); |
| insert into catdump.ddl SELECT distinct '4.1u', stmt from cat41; |
| drop external table cat41; |
| -- |
| -- Create the upgrade schema and fill that schema with 4.1 cat |
| -- then execute the upgrade script |
| -- |
| drop schema upg_catalog cascade; |
| ERROR: schema "upg_catalog" does not exist |
| CREATE SCHEMA upg_catalog; -- pg_catalog |
| SET SEARCH_PATH to upg_catalog; |
| drop external table execCmd; |
| ERROR: table "execcmd" does not exist |
| create external web table execCmd(a text) |
| execute 'psql -p $GP_MASTER_PORT -f @abs_srcdir@/data/upg_catupgrade_42.sql $GP_DATABASE $GP_USER >@abs_srcdir@/data/upgrade_exec.log 2>@abs_srcdir@/data/upgrade_err.log;' |
| on master format 'text'; |
| select * from execCmd; |
| a |
| --- |
| (0 rows) |
| |
| -- |
| -- Dump the upgraded upg_catalog to catdump |
| -- |
| select catdump.dump_to_table('4.1u', 'upg_catalog'); |
| dump_to_table |
| --------------- |
| 1 |
| (1 row) |
| |
| -- |
| -- Tidy up the dumped text |
| -- |
| update catdump.DDL set stmt = regexp_replace(ltrim(rtrim(stmt)), 'upg_catalog', 'pg_catalog', 'ig'); |
| select stmt from catdump.DDL where ver = '4.1u' |
| except |
| select stmt from catdump.DDL where ver = '4.2' |
| order by 1; |
| stmt |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| CREATE TYPE pg_catalog.anytable( INPUT = pg_catalog.anytable_in, OUTPUT = pg_catalog.anytable_out, INTERNALLENGTH = VARIABLE, STORAGE = extended, DELIMITER = ',', ALIGNMENT = double ); |
| CREATE UNIQUE INDEX pg_language_name_index ON pg_catalog.pg_language USING btree(lanname); |
| (2 rows) |
| |
| select stmt from catdump.DDL where ver = '4.2' |
| except |
| select stmt from catdump.DDL where ver = '4.1u' |
| order by 1; |
| stmt |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| CREATE AGGREGATE pg_catalog.pg_partition_oid(pg_catalog.oid, pg_catalog.record) ( SFUNC = pg_partition_oid_transfn, STYPE = pg_catalog.internal, FINALFUNC = pg_partition_oid_finalfn ); |
| CREATE FUNCTION pg_catalog.gpfusion_read() RETURNS pg_catalog.int4 LANGUAGE c STABLE AS '$libdir/gpfusion.so', 'gpfusionprotocol_import'; |
| CREATE FUNCTION pg_catalog.gpfusion_validate() RETURNS pg_catalog.void LANGUAGE c STABLE AS '$libdir/gpfusion.so', 'gpfusionprotocol_validate_urls'; |
| CREATE FUNCTION pg_catalog.gpfusion_write() RETURNS pg_catalog.int4 LANGUAGE c STABLE AS '$libdir/gpfusion.so', 'gpfusionprotocol_export'; |
| CREATE FUNCTION pg_catalog.gpfusionwritable_export(pg_catalog.record) RETURNS pg_catalog.bytea LANGUAGE c STABLE AS '$libdir/gpfusion.so', 'gpdbwritableformatter_export'; |
| CREATE FUNCTION pg_catalog.gpfusionwritable_import() RETURNS pg_catalog.record LANGUAGE c STABLE AS '$libdir/gpfusion.so', 'gpdbwritableformatter_import'; |
| CREATE TYPE pg_catalog.anytable( INPUT = pg_catalog.anytable_in, OUTPUT = pg_catalog.anytable_out, INTERNALLENGTH = VARIABLE, STORAGE = extended, DELIMITER = ',', ALIGNMENT = double ); DROP TYPE pg_catalog._anytable; |
| CREATE UNIQUE INDEX pg_compression_oid_index ON pg_catalog.pg_compression USING btree(oid); |
| CREATE UNIQUE INDEX pg_extprotocol_oid_index ON pg_catalog.pg_extprotocol USING btree(oid); |
| CREATE UNIQUE INDEX pg_filesystem_fsysname_index ON pg_catalog.pg_filesystem USING btree(fsysname); |
| CREATE UNIQUE INDEX pg_filesystem_oid_index ON pg_catalog.pg_filesystem USING btree(oid); |
| CREATE UNIQUE INDEX pg_language_lanname_index ON pg_catalog.pg_language USING btree(lanname); |
| (12 rows) |
| |
