Trying to Right an AND-ed SQL Expression-CodePudding

I'm trying to create a SQL expression for an INSERT policy for a resource_authors table (a JOIN table linking resources and users):

CREATE POLICY insert_resources_authors ON public.resource_authors
    FOR INSERT TO public_user
    WITH CHECK (*some expression*)

What I'd like to say is "the user must be logged in and there must not be an existing author record for that resource yet". I can express the first part using a custom current_user_id function I have:

current_user_id() IS NOT NULL

And I can get the second with:

SELECT count(user_id) > 0 
FROM resource_authors 
WHERE resource_id = resource_id

... but I can't figure out how to combine them. Can anyone clue me in to how I can select both, as a single boolean?

CodePudding user response：

Literally place an AND between them (and wrap them in parenthesis to make clear they're subqueries):

(current_user_id() IS NOT NULL)
AND
NOT (SELECT count(user_id) > 0 
  FROM resource_authors 
  WHERE resource_id = resource_id)

I'd write

CREATE POLICY insert_resources_authors ON public.resource_authors
    FOR INSERT TO public_user
    WITH CHECK (
        current_user_id() IS NOT NULL
        AND
        NOT EXISTS (SELECT *
            FROM resource_authors ra
            WHERE ra.resource_id = resource_authors.resource_id
        )
    );

However I find it questionable to allow anyone to make themselves author of any authorless resource. I'd rather record the author via a trigger when they create the resource.