Re: [GENERAL] Not possible to compare regrole in a view query? - Mailing list pgsql-general

Glen Huang <heyhgl@gmail.com> writes:
> I have this simple view definition:
> CREATE TEMP VIEW user_schema AS
>     SELECT nspname AS name FROM pg_namespace
>     WHERE nspname = 'public' OR nspowner = ‘rolename'::regrole;

> But it fails to create the view by complaining: constant of the type "regrole" cannot be used here

It's not that you can't compare it, it's that you can't store the
constant, for arcane reasons having to do with how the view's dependency
on the role name would need to be represented.

You can work around it like this:

CREATE TEMP VIEW user_schema AS
    SELECT nspname AS name FROM pg_namespace
    WHERE nspname = 'public' OR nspowner = 'rolename'::text::regrole;

Here, the stored constant is just a string of type text, and the lookup
in pg_authid will happen at runtime (and throw an error then, if you've
dropped the role).

            regards, tom lane