Rod, * Rod Taylor (rod.tay...@gmail.com) wrote: > My actual use-case involves a range. Most users can see and manipulate the > record when CURRENT_TIMESTAMP is within active_period. Some users > (staff/account admins) can see recently dead records too. And a 3rd group > (senior staff) have no time restriction, though there are a few customers > they cannot see due to their information being a touch more sensitive. > I've simplified the below rules to just deal with active_period and the > majority of user view (@> CURRENT_TIMESTAMP).
Interesting. > NOTE: the active_period range is '[)' by default, so records with upper() = > CURRENT_TIMESTAMP are not visible with @> CURRENT_TIMESTAMP restriction. Is that really what you intend/want though? For records with upper() = CURRENT_TIMESTAMP to not be visible? You are able to change the range returned from tstzrange by specifying what you want, eg: select tstzrange(current_timestamp, current_timestamp, '[]'); > CREATE A TABLE t (id integer, active_period tstzrange NOT NULL DEFAULT > tstzrange(current_timestamp, NULL)); Why NULL instead of 'infinity'...? > -- Disallowed due to hide_old_select policy. > UPDATE t SET active_period = tstzrange(lower(active_period), > CURRENT_TIMESTAMP); Guess I'm still trying to figure out if you really intend for this to make the records invisible to the 'most users' case. > I'm happy to help with testing and documentation but first I need to > understand what the intended functionality was. Right now it seems > inconsistent between the simple single policy version and the multi policy > version; the docs imply the single policy version is correct (it only seems > to mention SELECT checks on RETURNING clauses). I agree that the documentation could be improved here. Thanks! Stephen
signature.asc
Description: Digital signature
