On Mon, Feb 1, 2016 at 3:22 PM, Dane Foster <studdu...@gmail.com> wrote:
> > On Mon, Feb 1, 2016 at 2:41 PM, Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > >> On 02/01/2016 11:17 AM, Dane Foster wrote: >> >>> Hello, >>> >>> I'm discovering that I need to write quite a few functions for use >>> strictly w/ check constraints and I'm wondering if declaring the >>> volatility category for said functions will affect their behavior when >>> invoked by PostgreSQL's check constraint mechanism. >>> >>> Essentially what I'm trying to figure out is if volatility categories >>> increase or decrease the latency of executing check constraints. I've >>> done some micro benchmarks but I have no experience benchmarking >>> anything in PostgreSQL to trust that I'm measuring the right thing. So >>> I'm asking the experts. >>> >> >> The above is sort of backwards. You need to ask what the function does >> and from that determine what is the most appropriate volatitity category. >> For more detailed info see: >> >> http://www.postgresql.org/docs/9.5/interactive/xfunc-volatility.html >> > > I did that already and all of the ones written so far would be STABLE. > The gist of all of them is they check for the presence or absence of a > particular type of thing to exist in some other table. Unfortunately the > "type" definition can't be expressed as a primary key so I can't use > foreign keys to enforce consistency. > > > >> It would help to see some samples of the actual functions. > > -- $1: The class event primary key > -- $2: The discount code > CREATE FUNCTION discounts_enabled_for(INT, CITXT70) RETURNS BOOLEAN AS $$ > SELECT NOT > CASE (SELECT type FROM discount_codes WHERE code = $2) > WHEN 'giftcert'::DISC_CODE_TYPE THEN TRUE > WHEN 'coupon'::DISC_CODE_TYPE > THEN (SELECT no_coupons FROM class_events WHERE cid = $1) > ELSE (SELECT no_vouchers FROM class_events WHERE cid = $1) > END; > $$ LANGUAGE SQL STRICT LEAKPROOF; > COMMENT ON FUNCTION discounts_enabled_for(INT, CITXT70) IS > $$Determines if a class event accepts coupon or voucher discounts.$$; > > CREATE TABLE group_codes ( > cid INTEGER > PRIMARY KEY > REFERENCES class_events ON DELETE CASCADE ON UPDATE CASCADE, > code CITXT70 > NOT NULL > REFERENCES discount_codes ON DELETE CASCADE ON UPDATE CASCADE, > CONSTRAINT discounts_enabled CHECK (discounts_enabled_for(cid, code)) > ); > CREATE INDEX ON group_codes USING GIN (code); > COMMENT ON TABLE group_codes IS > $$Discount codes that are exclusive to a set of class events.$$; > I just realized there is little bug in the function body. So before anyone gets distracted by it I wanted to let you know that I know it exists and has been fixed internally. >
