On 4/25/25 14:01, Vydehi Ganti wrote:
> This is my Scenario:
>
> 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'));
side note : it seems ptable_name and powner are not read in your function
