plgpsql - is it possible to declare setof record or table variable?
plpgsql, postgresql, postgresql-8.4
Solution
I think this simplified SQL function might do what you are looking for:
CREATE FUNCTION permission_cache_update(affected_user_list int[])
RETURNS void AS
$func$
DELETE FROM permission_cache p
USING (SELECT unnest($1) AS u_id) a
WHERE p.user_id = a.u_id;
INSERT INTO permission_cache (user_id, permission_id)
SELECT u.user_id, r.permission_id
FROM user_role u
JOIN role_permission r USING (role_id)
JOIN (SELECT unnest($1) AS u_id) a ON a.u_id = u.user_id
UNION
SELECT p.user_id, p.permission_id
FROM user_permission p
JOIN (SELECT unnest($1) AS u_id) a ON a.u_id = p.user_id;
$func$ LANGUAGE sql;
Writable CTE (a.k.a. data-modifying CTE) would simplify this tremendously but were only introduced with Postgres 9.1. Once again, your outdated version is in the way.
Referencing parameters by name was only introduced in Postgres 9.2 for SQL functions (earlier for plpgsql). So I use the positional parameter `$1`.
Problem
I have the following - not working - function: ``` CREATE FUNCTION permission_cache_update(affected_user_list int[]) RETURNS TABLE(user_id INT4, permission_id INT4) AS $BODY$ DECLARE current_relations SETOF RECORD; BEGIN WITH affected_user AS ( SELECT unnest(affected_user_list) AS u_id ), affected_relations AS ( SELECT user_role.user_id, role_permission.permission_id FROM user_role JOIN role_permission ON user_role.role_id = role_permission.role_id JOIN affected_user ON affected_user.u_id = user_role.user_id UNION SELECT user_permission.user_id, user_permission.permission_id FROM user_permission JOIN affected_user ON affected_user.u_id = user_permission.user_id ) SELECT affected_relations.user_id, affected_relations.permission_id FROM affected_relations INTO current_relations; DELETE FROM permission_cache WHERE ARRAY[user_id] <@ affected_user_list; INSERT INTO permission_cache (user_id, permission_id) SELECT user_id, permission_id FROM current_relations; END $BODY$ LANGUAGE plpgsql; ``` I want to store the current user-permission relations in `current_relations` as (INT4, INT4). Is it possible to do this with variables without loops and temporary tables? I'll use something like this later, so I really need it as variables, not as subquery ``` DELETE FROM permission_cache WHERE ARRAY[user_id] <@ affected_user_list AND NOT IN (SELECT user_id, permission_id FROM current_relations); INSERT INTO permission_cache (user_id, permission_id) SELECT user_id, permission_id FROM current_relations WHERE NOT EXIST (SELECT user_id, permission_id FROM permission_cache); ``` I think it is possible with table -> 2d array conversions, but that's complicated, so if it possible with records, it would be better... Solution: It was easier to create a loop with 1d array instead of using records or 2d arrays: ``` CREATE FUNCTION permission_cache_update( IN affected_user_list INT4 [] ) RETURNS VOID AS $BODY$ DECLARE user_index INT4; current_user_id INT4; current_permission_relations INT4 []; deleted_permission_relations INT4 []; inserted_permission_relations INT4 []; BEGIN FOR user_index IN 1 .. array_upper(affected_user_list, 1) LOOP current_user_id := affected_user_list[user_index]; WITH user_permission_summary AS ( SELECT role_permission.permission_id FROM user_role, role_permission WHERE role_permission.role_id = user_role.role_id AND user_role.user_id = current_user_id UNION SELECT user_permission.permission_id FROM user_permission WHERE user_permission.user_id = current_user_id ) SELECT array_agg(permission_id) FROM user_permission_summary INTO current_permission_relations; SELECT array_agg(permission_cache.permission_id) FROM permission_cache WHERE permission_cache.user_id = current_user_id AND ( current_permission_relations IS NULL OR NOT (ARRAY [permission_cache.permission_id] <@ current_permission_relations) ) INTO deleted_permission_relations; SELECT array_agg(inserted_permission_id) FROM unnest(current_permission_relations) AS inserted_permission_id WHERE NOT EXISTS(SELECT 1 FROM permission_cache WHERE permission_cache.user_id = current_user_id AND permission_cache.permission_id = inserted_permission_id) INTO inserted_permission_relations; DELETE FROM permission_cache WHERE permission_cache.user_id = current_user_id AND permission_cache.permission_id = ANY (deleted_permission_relations); INSERT INTO permission_cache (user_id, permission_id) SELECT current_user_id, inserted_permission_id FROM unnest(inserted_permission_relations) AS inserted_permission_id; END LOOP; END; $BODY$ LANGUAGE plpgsql VOLATILE; ``` off: Pplpgsql is a very poor language with an even poor documentation, I don't like it... Sorry for the code formatting, autoformat in my IDE is not so good... :S