Best way to cast a VIEW row type to the underlying TABLE row type
1
vote
1
answer
669
views
I have a table with an index and a row-level security policy. Due to [this problem](https://dba.stackexchange.com/q/232789/188406) (more details: (https://stackoverflow.com/q/48230535/1048572) , (https://www.postgresql.org/message-id/flat/2811772.0XtDgEdalL@peanuts2) , (https://stackoverflow.com/q/63008838/1048572) , (https://www.postgresql.org/message-id/flat/CAGrP7a2t+JbeuxpQY+RSvNe4fr3+==UmyimwV0GCD+wcrSSb=w@mail.gmail.com) , (https://stackoverflow.com/q/48230535/1048572)) , the index is not used when the policy applies, which makes my queries unbearably slow.
The workaround I am contemplating would be to create a
VIEW
with security_invoker = false
and security_barrier = false
. (If I do enable the security_barrier
, the query again doesn't use the index).
The problem I am facing now is that I cannot just change the queries to use FROM my_view AS example
instead of FROM my_table AS example
, since some of them use functions that are defined to take the my_table
composite type. A simplified example:
CREATE TABLE example (
id int,
name text,
is_visible boolean
);
CREATE VIEW test AS SELECT * FROM example WHERE is_visible;
CREATE FUNCTION prop(e example) RETURNS text LANGUAGE SQL AS $$ SELECT e.id::text || ': ' || e.name; $$;
SELECT e.prop FROM example e; -- works
SELECT e.prop FROM test e; -- ERROR: column e.prop does not exist
([online demo](https://dbfiddle.uk/cb0bn3NV))
Now the question is **how to cast the rows to the expected type?** There is [this question](https://dba.stackexchange.com/q/247240/188406) and I also found a way to do this using the ROW
constructor, but I'm not certain how good this is:
SELECT e.prop FROM (SELECT (ROW(test.*)::example).* FROM test) e;
It's nice that I can just use it as a drop-in replacement for the table expression (without changing anything else in the query), and it does work (postgres accepts it and does use my index when I have the respective WHERE
clause), but it looks horrible. Are there problems with my approach that I am missing? Is there a better solution?
Asked by Bergi
(514 rep)
Oct 18, 2022, 11:14 AM
Last activity: Aug 6, 2025, 03:06 PM
Last activity: Aug 6, 2025, 03:06 PM