Re: Clarification on RLS policy - Mailing list pgsql-general

This is my Scenario:

I would need a policy on table Activity which has a column country_code .
In the policy i would need to call a function get_country as below which queries the users table based on current user and checks which country code that user has access to.
Then it should build up the lPredicate with the filter condition and append to the query user runs on the Activity table.
Can you please guide how to achieve this?

CREATE OR REPLACE FUNCTION one.get_country(
powner name,
ptable_name name)
    RETURNS character varying
LANGUAGE 'plpgsql'
    COST 100
    STABLE PARALLEL UNSAFE
AS $BODY$
DECLARE

    lOSUser         varchar(4000) := UPPER(SUBSTRING(current_user FROM POSITION('\' IN current_user) + 1));
    lPredicate      varchar(4000) := NULL;
    lCount          integer;

  i RECORD;

BEGIN
    IF position('ro' in current_user) = 0 THEN
        lPredicate := '1=1';
    ELSE
        -- Users associated to explicit country_code
        FOR i IN (SELECT r.country_code AS country_code
                    FROM one.users  u
                        where UPPER(SUBSTR(u.Login, INSTR(u.Login, '\', -1) + 1)) = lOSUser )
                    WHERE u.role_type = 'reader') LOOP
            lPredicate := lPredicate||''''||i.country_code||''',';
        END LOOP;

        IF lPredicate IS NOT NULL THEN
            lPredicate := 'SUBSTR("id",1,3) IN ('||SUBSTR(lPredicate, 1, LENGTH(lPredicate)-1)||')';
        ELSE
            lPredicate := '1=1';
        END IF;
    END IF;

    RETURN lPredicate;

END;
$BODY$;

For the below policy statement it created the policy but i cant call that
CREATE POLICY "Codebase_Filter"
    ON one.activity
    FOR SELECT
    TO one
    USING (one.get_country('one','activity'));