Postgres | IF statement with OR conditional

Question

I have a stored procedure which is used to validate an incoming variable. If the variable is not 'maker' or 'member', then it should throw an error. If the variable is of the two above mentioned, it should continue normal processing.

The current issue with the below code is that it always returns false, therefore always throwing an error, no mater if the variable is of 'maker', 'member', or something else.

Any ideas on where I may be looking at my logic structure wrongly?

CREATE OR REPLACE FUNCTION validate_creator_type (
  "creator_type" VARCHAR(25)         -- $1
) RETURNS VOID AS $$
DECLARE
  _maker VARCHAR(25) := 'maker';
  _member VARCHAR(25) := 'member';
BEGIN

  -- Validate the creator type is allowed
  IF ($1 <> _maker) OR ($1 <> _member) THEN
    RAISE EXCEPTION 'Invalid creator type ---> %', $1
      USING HINT = 'The creator type can only be member or maker';
  END IF;

END $$
SECURITY DEFINER
LANGUAGE plpgsql;

score 2 · Accepted Answer · answered Jun 26 '18 at 13:07

Change OR to AND and your problem should gone

    CREATE OR REPLACE FUNCTION validate_creator_type (
      "creator_type" VARCHAR(25)         -- $1
    ) RETURNS VOID AS $$
    DECLARE
      _maker VARCHAR(25) := 'maker';
      _member VARCHAR(25) := 'member';
    BEGIN

      -- Validate the creator type is allowed
      IF ($1 <> _maker) AND ($1 <> _member) THEN
        RAISE EXCEPTION 'Invalid creator type ---> %', $1
          USING HINT = 'The creator type can only be member or maker';
      END IF;

    END $$
    SECURITY DEFINER
    LANGUAGE plpgsql;

@BillBensing: alternatively `if ($1 not in (_maker, _member)) then` — , Jun 26 '18 at 13:41

Postgres | IF statement with OR conditional

1 Answers1