0

i have a table with rls on and obviously that returns only the rows where the id is the same as the users is there a way to return a default set of rows if the returned set is null. so id 1 would return where id = 1 but for 2 if the table had no id 2 would return default set x. the only way i can think of doing it is like this 

 create policy name on table using (member_id = (SELECT MAX(COALESCE(member_id, -1)) 
             FROM dim_bucket 
             WHERE 'usr_cashtarget_' || member_id = CURRENT_USER)

this method feels 'dirty' so was wondering if there was a nicer way to di it when creating the policy

jwolverson
  • 67
  • 8
  • You can create view on top of it that returns a default row in case none is found in the underlying table. Then, you'll need to query the view, though. – The Impaler May 01 '20 at 12:50
  • would a second policy be able to do it without the need for a view ? – jwolverson May 01 '20 at 14:41
  • i tested my method above and it doesnt work it creates an infinite recursion error. @TheImpaler how would i do it with a view ? – jwolverson May 03 '20 at 14:13

0 Answers0