Обсуждение: [GENERAL] Not possible to compare regrole in a view query?

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

If I run the query directly, I get the correct result.

Does that mean I can’t compare role name this way in view? Should I use join to work around it?

Thanks.

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

Thank you! It works.

I’d very much like to know why this solution works. Could you please elaborate a bit more? I can’t quite understand
whatyou mean by "can’t store the constant”. Since a view can’t store data, do you mean it can’t have a query that
containsa constant? But isn’t 'public'  a constant too? 

Also what’s the difference between ‘rolename'::regrole and 'rolename'::text::regrole? Is it correct that in the former
case,the conversion between the constant string and regrole happens in the sql parse time whereas in the latter case it
happensin the run time? (Probably wrong, since looks like it can happen in the parse time too) Why it could work around
the"cannot be used here” issue? 

Thanks.

> On 5 Sep 2017, at 8:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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