Not possible to compare regrole in a view query?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 what you mean by "can’t store the constant”. Since a view can’t store data, do you mean it can’t have a query that contains a 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 happens in 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general