Re: Question about RLS policy

Please keep the list on CC.

Garry Chen wrote:
> Thank you very much for your quick reply. I have a stored procedure or function
> (getpredicate2) as following and would like to use it inside using() so when I create
> policy as
> create policy plc_acct_nbr2 on distribution_fc for select to public using (acct_nbr in
> (select getpredicate2())) it return error as:
> ERROR: operator does not exist: character varying = acct_nbr_lst
> HINT: No operator matches the given name and argument type(s). You might need to add
> explicit type casts.
> ********** Error **********
>
> ERROR: operator does not exist: character varying = acct_nbr_lst
> SQL state: 42883
> Hint: No operator matches the given name and argument type(s). You might need to add
> explicit type casts.
>
>
> CREATE OR REPLACE FUNCTION public.getpredicate2(
> )
> RETURNS SETOF acct_nbr_lst
[...]

Your function returns "SETOF acct_nbr_lst", and "distribution_fc.acct_nbr" seems
to be of type character varying.

PostgreSQL does not know how to compare these two types, you'll have to tell it.

Either cast "acct_nbr" to "acct_nbr_lst", or have the function return
"SETOF character varying", or (more complicated) create an operator named "="
that can compare these two data types.

Yours,
Laurenz Albe