CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean
AS $$
BEGIN
    EXECUTE format('SELECT * FROM (%s) AS test', stmt);
    RETURN true;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE;
        RETURN false;
END
$$
    LANGUAGE plpgsql
    STRICT;

ALTER TABLE sys_admin.published_services
    ADD schema varchar;
UPDATE sys_admin.published_services
    SET schema = substring(CAST(name AS varchar) from '(.*)\.');
ALTER TABLE sys_admin.published_services
    ADD CHECK(to_regnamespace(schema) IS NOT NULL),
    ALTER name TYPE varchar
        USING substring(CAST(name AS varchar) from '\.(.*)'),
    DROP CONSTRAINT published_services_pkey,
    ADD PRIMARY KEY (schema, name),
    ADD view_def text CHECK (is_valid_from_item(view_def)),
    ADD CHECK (to_regclass(schema || '.' || name) IS NOT NULL
            OR view_def IS NOT NULL);