0

While implementing a sharelikes table I added a procedure to add a new record for a like.

I wanted to add a check before insert that the item being liked does not belong to the users who submitted the like request. Given function parameters Pshareid and Puserid I used the following conditional in the procedure

SELECT INTO checkuser true FROM ks.shares 
WHERE id = Pshareid AND userid = Puserid limit 1;
IF checkuser THEN
  -- Cannot like your own share
ELSE
    --INSERT the new record
END IF;

In the select described above the record will almost always not exist.

Is that more efficient than a SELECT on the share id to return the record (if it exists which is very likely at the point of select) and then to check the userid of the returned record?

SELECT userid INTO checkuser FROM ks.shares 
WHERE id = Pshareid limit 1;
IF checkuser = Puserid THEN
    -- Cannot like your own share
ELSE
    --INSERT the new record
END IF;
0

You can use PERFORM 1 test with a control statement using FOUND special variable. Your query will get like this;

PERFORM 1 
    FROM ks.shares 
    WHERE
        id = Pshareid AND userid = Puserid
    LIMIT 1;

IF FOUND THEN
  -- Cannot like your own share
ELSE
    --INSERT the new record
END IF;

Referenced sections;

Executing a Command With No Result

Available Diagnostics Items (special variable named FOUND)

  • Thank you for a different approach. Do you have any sense of if that would be more efficient than selecting the share record and checking the userid value, given that almost always the share will not belong to the given userid and no record will be found? – NectarSoft May 29 '18 at 9:16
  • Should I add an composite index on the id and userid columns perhaps? – NectarSoft May 29 '18 at 9:16
  • I need to know your table structure for recommending an index. You didn't provide it. So I assumed there was one already. If you add an unique index (id, userid) that perform 1 query will do index only scan which is fastest. Again I assume id is unique. – Sahap Asci May 29 '18 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.