Re: comparison trigger function - Mailing list pgsql-novice

From Andrew Hammond
Subject Re: comparison trigger function
Date
Msg-id 4278D47A.6030207@ca.afilias.info
Whole thread Raw
In response to comparison trigger function  ("Keith Worthington" <keithw@narrowpathinc.com>)
List pgsql-novice
Not really. You can require this with a table constraint,

CREATE TABLE boxes (
    length NOT NULL,
    width NOT NULL,
    CONSTRAINT shape CHECK (length > width)
);

But that won't automatically switch them.

Keith Worthington wrote:
> Hi All,
>
> I just built the trigger function below to gaurantee that the width entered in
> a table is always less than the length.  It seems to work but I would like to
> know if there is a better (more concise, faster) way of providing this
> functionality.
>
> Kind Regards,
> Keith
>
> -- Function: tf_width_lt_length()
>
> -- DROP FUNCTION tf_width_lt_length();
>
> CREATE OR REPLACE FUNCTION tf_width_lt_length()
>   RETURNS "trigger" AS
> $BODY$
>    DECLARE
>       v_quantity FLOAT4;
>    BEGIN
> --    If width is greater than length reverse their values.
>       IF NEW.width_in > NEW.length_in THEN
>          v_quantity := NEW.width_in;
>          NEW.width_in := NEW.length_in;
>          NEW.length_in := v_quantity;
>       END IF;
>       RETURN NEW;
>    END;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>
> ALTER FUNCTION tf_width_lt_length() OWNER TO postgres;
> GRANT EXECUTE ON FUNCTION tf_width_lt_length() TO postgres;
> GRANT EXECUTE ON FUNCTION tf_width_lt_length() TO public;
>
> -- Trigger: tgr_width_lt_length on tbl_data
>
> -- DROP TRIGGER tgr_width_lt_length ON tbl_data
>
> CREATE TRIGGER tgr_width_lt_length
>   BEFORE INSERT OR UPDATE
>   ON tbl_data
>   FOR EACH ROW
>   EXECUTE PROCEDURE tf_width_lt_length();
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

pgsql-novice by date:

Previous
From: Andrew Hammond
Date:
Subject: Re: Can't Use DB As Template - accessed by other users error
Next
From: David
Date:
Subject: Output of PL/PGSQL?