From: | Andrew Hammond <ahammond(at)ca(dot)afilias(dot)info> |
---|---|
To: | KeithW(at)narrowpathinc(dot)com |
Cc: | PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: comparison trigger function |
Date: | 2005-05-04 13:56:10 |
Message-ID: | 4278D47A.6030207@ca.afilias.info |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | David | 2005-05-04 15:39:47 | Output of PL/PGSQL? |
Previous Message | Andrew Hammond | 2005-05-04 13:41:34 | Re: Can't Use DB As Template - accessed by other users error |