[GENERAL] Row level security policy - calling function for right hand sidevalue of 'in' in using_expression - Mailing list pgsql-general

I have a RLS policy definition like:

         CREATE POLICY promoter_policy ON Agency
         USING (promoter in build_valid_promoter_list())
         WITH CHECK (promoter in build_valid_promoter_list());


The build_valid_promoter_list function definition is:

         CREATE OR REPLACE FUNCTION build_valid_promoter_list() RETURNS
TABLE(id BIGINT)
           LANGUAGE plpgsql
         AS $$
            DECLARE
                    promoter_id BIGINT;
                    acct_role TEXT;
            BEGIN
              SELECT promoter, role INTO promoter_id, acct_role FROM
PromoterAccount
              WHERE oid = substring(current_setting('ts.promoter',true),
26)::BIGINT;

              IF acct_role = 'agency' THEN
                 RETURN QUERY SELECT UNNEST(customers) FROM Agency WHERE
promoter = promoter_id;
              ELSE
                 RETURN QUERY SELECT promoter_id;
              END IF;
            END
         $$;



And I have one problem and one concern;

- When I try to create the policy using the first code fragment, I got
'ERROR:  syntax error at or near "build_valid_promoter_list"'. I am
wondering the reason and how to fix it.

- Ideally, it would be great if the function build_valid_promoter_list()
get called once and the RLS internal uses it as a constant value. Is
this possible?



I am very new to RLS, any hints, opinions, and fixes will be greatly
appreciated.



Thanks

- Jong-won