Skip to content

Instantly share code, notes, and snippets.

@xacrimon
Created November 9, 2023 13:48
Show Gist options
  • Save xacrimon/2bb5957fa7cbf5425bcc401e904ea474 to your computer and use it in GitHub Desktop.
Save xacrimon/2bb5957fa7cbf5425bcc401e904ea474 to your computer and use it in GitHub Desktop.
```sql
CREATE OR REPLACE FUNCTION get_access_path(
p_column_filter TEXT DEFAULT NULL,
p_value_filter TEXT DEFAULT NULL
)
RETURNS TABLE
(
"user" bivar[],
user_user_group bibi[],
user_group bivar[],
action action_path_type[],
resource_group bivar[],
resource_group_resource bibi[],
resource bivarvar[]
)
AS
$$
DECLARE
v_sql TEXT;
BEGIN
-- Check if the argument is NULL
IF p_column_filter IS NULL THEN
-- Raise an application error with a custom error message
RAISE EXCEPTION 'Argument p_column_filter cannot be null';
END IF;
IF p_value_filter IS NULL THEN
-- Raise an application error with a custom error message
RAISE EXCEPTION 'Argument p_value_filter cannot be null';
END IF;
v_sql := '
select array_agg(distinct (users.user_id, users.username)::bivar) as users,
array_agg(distinct
(users_usergroup.user_id, users_usergroup.user_group_id)::bibi) as user_user_group,
array_agg(distinct (usergroup.user_group_id, usergroup.name)::bivar) as user_group,
array_agg(distinct
(action.action_id, action.name, action.type, action.user_group_id, action.resource_group_id)::action_path_type) as action,
array_agg(distinct
(resourcegroup.resource_group_id, resourcegroup.name)::bivar) as resource_group,
array_agg(distinct (resourcegroup_resource.resource_group_id, resourcegroup_resource.resource_id)::bibi) as resource_group_resource,
array_agg(distinct (resource.resource_id, resource.name, resource.hostname)::bivarvar) as resource
from users
join users_usergroup on users_usergroup.user_id = users.user_id
join usergroup on users_usergroup.user_group_id = usergroup.user_group_id
join action on action.user_group_id = usergroup.user_group_id
join resourcegroup on resourcegroup.resource_group_id = action.resource_group_id
join resourcegroup_resource
on resourcegroup_resource.resource_group_id = resourcegroup.resource_group_id
join resource on resource.resource_id = resourcegroup_resource.resource_id';
v_sql := v_sql || ' WHERE action.type = ''allowed'' and ' || p_column_filter || ' = $1';
RETURN QUERY EXECUTE v_sql USING p_value_filter;
END ;
$$ LANGUAGE plpgsql;```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment