How to move a table from one schema to another schema

Post date: Sep 24, 2014 6:17:13 PM

CREATE OR REPLACE FUNCTION move_relation(VARCHAR, VARCHAR, VARCHAR)

RETURNS BOOL

AS '

-- $1 is the table name

-- $2 is the source schema

-- $3 is the destination schema

--

        UPDATE pg_catalog.pg_class

        SET relnamespace = (SELECT oid FROM pg_catalog.pg_namespace

                                WHERE nspname = $3)

        WHERE relnamespace = (SELECT oid FROM pg_catalog.pg_namespace

                                WHERE nspname = $2)

                AND relname = $1;

UPDATE pg_catalog.pg_type

        SET typnamespace = (SELECT oid FROM pg_catalog.pg_namespace

                                WHERE nspname = $3)

        WHERE typnamespace = (SELECT oid FROM pg_catalog.pg_namespace

                                WHERE nspname = $2)

                AND typname = $1;

        SELECT TRUE;

' LANGUAGE SQL;