currency.sql.in

/* contrib/currency/currency.sql.in */

-- Adjust this setting to control where the objects get created.
SET search_path = public;

-----------------------------------------------------------------------------
--                            CURRENCY_CODE                                --
-----------------------------------------------------------------------------

--
--	Input and output functions and the type itself:


CREATE TYPE tla;

CREATE OR REPLACE FUNCTION tla_in(cstring)
	RETURNS tla
	AS 'currency'
	LANGUAGE C STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION tla_out(tla)
	RETURNS cstring
	AS 'currency'
	LANGUAGE C STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION tla_send(tla)
	RETURNS bytea
	AS 'int2send'
	LANGUAGE internal STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION tla_recv(internal)
	RETURNS tla
	AS 'int2recv'
	LANGUAGE internal STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION tla_in_text(text)
	RETURNS tla
	AS 'currency'
	LANGUAGE C STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION tla_out_text(tla)
	RETURNS text
	AS 'currency'
	LANGUAGE C STRICT IMMUTABLE;

CREATE TYPE tla (
	INPUT = tla_in,
	OUTPUT = tla_out,
-- values of internallength, passedbyvalue, alignment, and storage are copied from the named type.
	LIKE = int2,
	SEND = tla_send,
	RECEIVE = tla_recv,
-- string category, to automatically try string conversion etc
	CATEGORY = 'S',
	PREFERRED = false
);

--
--	Comparison functions
--
CREATE OR REPLACE FUNCTION eq(tla, tla)
	RETURNS bool
	AS 'int2eq'
	LANGUAGE internal STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION ne(tla, tla)
	RETURNS bool
	AS 'int2ne'
	LANGUAGE internal STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION le(tla, tla)
	RETURNS bool
	AS 'int2le'
	LANGUAGE internal STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION lt(tla, tla)
	RETURNS bool
	AS 'int2lt'
	LANGUAGE internal STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION ge(tla, tla)
	RETURNS bool
	AS 'int2ge'
	LANGUAGE internal STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION gt(tla, tla)
	RETURNS bool
	AS 'int2gt'
	LANGUAGE internal STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION btcmp_tla(tla, tla)
	RETURNS int4
	AS 'btint2cmp'
	LANGUAGE internal STRICT IMMUTABLE;

-- this function seems to "hash" the int2 to a much bigger size; eg
-- 1 => -1905060026
CREATE OR REPLACE FUNCTION hash_tla(tla)
	RETURNS int4
	AS 'hashint2'
	LANGUAGE internal STRICT IMMUTABLE;
--
--	Now the operators.
--
CREATE OPERATOR = (
	leftarg = tla,
	rightarg = tla,
	negator = <>,
	procedure = eq,
	restrict = eqsel,
	commutator = =,
	join = eqjoinsel,
	hashes, merges
);

CREATE OPERATOR <> (
	leftarg = tla,
	rightarg = tla,
	negator = =,
	procedure = ne,
	restrict = neqsel,
	join = neqjoinsel
);

CREATE OPERATOR #<# (
	leftarg = tla,
	rightarg = tla,
	negator = #>=#,
	procedure = lt
);

CREATE OPERATOR #<=# (
	leftarg = tla,
	rightarg = tla,
	negator = #>#,
	procedure = le
);

CREATE OPERATOR #># (
	leftarg = tla,
	rightarg = tla,
	negator = #<=#,
	procedure = gt
);

CREATE OPERATOR #>=# (
	leftarg = tla,
	rightarg = tla,
	negator = #<#,
	procedure = ge
);

--
--  Implicit and assignment type casts.
--

-- ...not sure yet
CREATE CAST (tla AS text)    WITH FUNCTION tla_out_text(tla) AS IMPLICIT;
CREATE CAST (text AS tla)    WITH FUNCTION tla_in_text(text) AS IMPLICIT;

--
-- The btree indexing operator class.
--

CREATE OPERATOR CLASS tla_ops
DEFAULT FOR TYPE TLA USING btree AS
    OPERATOR    1   #<#  (tla, tla),
    OPERATOR    2   #<=# (tla, tla),
    OPERATOR    3   =    (tla, tla),
    OPERATOR    4   #>=# (tla, tla),
    OPERATOR    5   #>#  (tla, tla),
    FUNCTION    1   btcmp_tla(tla, tla);

--
-- The hash indexing operator class.
--

CREATE OPERATOR CLASS tla_ops
DEFAULT FOR TYPE tla USING hash AS
    OPERATOR    1   =  (tla, tla),
    FUNCTION    1   hash_tla(tla);
--

COMMENT ON TYPE tla IS 'three-letter codes in an int2';

--
-- the 'currency' type
--
CREATE TYPE currency;

CREATE OR REPLACE FUNCTION currency_in_cstring(cstring)
	RETURNS currency
	AS 'currency'
	LANGUAGE C STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION currency_out_cstring(currency)
	RETURNS cstring
	AS 'currency'
	LANGUAGE C STRICT IMMUTABLE;

CREATE TYPE currency (
	INPUT = currency_in_cstring,
	OUTPUT = currency_out_cstring,
-- values of internallength, passedbyvalue, alignment, and storage are copied from the named type.
	INTERNALLENGTH = variable,
-- string category, to automatically try string conversion etc
	CATEGORY = 'S',
	PREFERRED = false
);

CREATE OR REPLACE FUNCTION code(currency)
	RETURNS tla
	AS 'currency', 'currency_code'
	LANGUAGE C STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION value(currency)
	RETURNS numeric
	AS 'currency', 'currency_value'
	LANGUAGE C STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION currency(numeric, tla)
	RETURNS currency
	AS 'currency', 'currency_compose'
	LANGUAGE C STRICT IMMUTABLE;

CREATE TABLE currency_rate (
       -- not TLA for bootstrapping reasons
       code TLA NOT NULL,
       primary key (code),
       minor int2 NOT NULL CHECK (minor <= 9 AND minor >= 0),
       symbol varchar(3) NULL,
       rate numeric NOT NULL,
       is_exchange boolean not null default 'f',
       CHECK (NOT is_exchange OR rate = 1),
       description text
);

-- functions below are dependent on the currency_rate table (this
-- doesn't matter, just for reference )
CREATE OR REPLACE FUNCTION format(currency)
	RETURNS cstring
	AS 'currency', 'currency_format'
	LANGUAGE C STRICT STABLE;

CREATE OPERATOR # (
	rightarg = currency,
	procedure = format
);

CREATE OR REPLACE FUNCTION change(currency, tla)
	RETURNS currency
	AS 'currency', 'currency_convert'
	LANGUAGE C STRICT STABLE;

CREATE OPERATOR -> (
	leftarg = currency,
	rightarg = tla,
	procedure = change
);
CREATE OR REPLACE FUNCTION money(currency)
	RETURNS money
	AS 'currency', 'currency_money'
	LANGUAGE C STRICT STABLE;

CREATE CAST (currency AS money) WITH FUNCTION money(currency);

CREATE OR REPLACE FUNCTION currency_numeric(currency)
	RETURNS numeric
	AS 'currency', 'currency_numeric'
	LANGUAGE C STRICT STABLE;

CREATE CAST (currency AS numeric) WITH FUNCTION currency_numeric(currency);

CREATE OR REPLACE FUNCTION eq(currency, currency)
	RETURNS boolean
	AS 'currency', 'currency_eq'
	LANGUAGE C STRICT STABLE;

CREATE OR REPLACE FUNCTION ne(currency, currency)
	RETURNS boolean
	AS 'currency', 'currency_ne'
	LANGUAGE C STRICT STABLE;

CREATE OR REPLACE FUNCTION le(currency, currency)
	RETURNS boolean
	AS 'currency', 'currency_le'
	LANGUAGE C STRICT STABLE;

CREATE OR REPLACE FUNCTION lt(currency, currency)
	RETURNS boolean
	AS 'currency', 'currency_lt'
	LANGUAGE C STRICT STABLE;

CREATE OR REPLACE FUNCTION ge(currency, currency)
	RETURNS boolean
	AS 'currency', 'currency_ge'
	LANGUAGE C STRICT STABLE;

CREATE OR REPLACE FUNCTION gt(currency, currency)
	RETURNS boolean
	AS 'currency', 'currency_gt'
	LANGUAGE C STRICT STABLE;

CREATE OR REPLACE FUNCTION btcmp_currency(currency, currency)
	RETURNS int4
	AS 'currency', 'currency_btcmp'
	LANGUAGE C STRICT STABLE;

CREATE OPERATOR = (
	leftarg = currency,
	rightarg = currency,
	negator = <>,
	procedure = eq,
	restrict = eqsel,
	commutator = =,
	join = eqjoinsel,
	hashes, merges
);

CREATE OPERATOR <> (
	leftarg = currency,
	rightarg = currency,
	negator = =,
	procedure = ne,
	restrict = neqsel,
	join = neqjoinsel
);

CREATE OPERATOR < (
	leftarg = currency,
	rightarg = currency,
	negator = >=,
	procedure = lt
);

CREATE OPERATOR <= (
	leftarg = currency,
	rightarg = currency,
	negator = >,
	procedure = le
);

CREATE OPERATOR > (
	leftarg = currency,
	rightarg = currency,
	negator = <=,
	procedure = gt
);

CREATE OPERATOR >= (
	leftarg = currency,
	rightarg = currency,
	negator = <,
	procedure = ge
);

CREATE OR REPLACE FUNCTION hash_currency(currency)
	RETURNS int4
	AS 'currency', 'currency_hash'
	LANGUAGE C STRICT STABLE;

CREATE OPERATOR CLASS currency_ops_hash
DEFAULT FOR TYPE currency USING hash AS
    OPERATOR    1   =  (currency, currency),
    FUNCTION    1   hash_currency(currency);

CREATE OPERATOR CLASS currency_ops
DEFAULT FOR TYPE currency USING btree AS
    OPERATOR    1   <  (currency, currency),
    OPERATOR    2   <= (currency, currency),
    OPERATOR    3   =  (currency, currency),
    OPERATOR    4   >= (currency, currency),
    OPERATOR    5   >  (currency, currency),
    FUNCTION    1   btcmp_currency(currency, currency);

CREATE OR REPLACE FUNCTION "(+)"(currency, currency)
	RETURNS currency
	AS 'currency', 'currency_add'
	LANGUAGE C STRICT STABLE;

CREATE OPERATOR + (
	leftarg = currency,
	rightarg = currency,
	commutator = +,
	procedure = "(+)"
);

CREATE OR REPLACE FUNCTION "(-)"(currency, currency)
	RETURNS currency
	AS 'currency', 'currency_sub'
	LANGUAGE C STRICT STABLE;

CREATE OPERATOR - (
	leftarg = currency,
	rightarg = currency,
	procedure = "(-)"
);

CREATE OR REPLACE FUNCTION "(*)"(currency, numeric)
	RETURNS currency
	AS 'currency', 'currency_mul'
	LANGUAGE C STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION "(*)"(numeric, currency)
	RETURNS currency
	AS 'currency', 'currency_mul'
	LANGUAGE C STRICT IMMUTABLE;

CREATE OPERATOR * (
	leftarg = currency,
	rightarg = numeric,
	commutator = *,
	procedure = "(*)"
);

CREATE OPERATOR * (
	leftarg = numeric,
	rightarg = currency,
	commutator = *,
	procedure = "(*)"
);

CREATE OR REPLACE FUNCTION "(/)"(currency, numeric)
	RETURNS currency
	AS 'currency', 'currency_div'
	LANGUAGE C STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION "(/)"(currency, currency)
	RETURNS numeric
	AS 'currency', 'currency_div'
	LANGUAGE C STRICT STABLE;

CREATE OPERATOR / (
	leftarg = currency,
	rightarg = currency,
	procedure = "(/)"
);

CREATE OPERATOR / (
	leftarg = currency,
	rightarg = numeric,
	procedure = "(/)"
);

CREATE OR REPLACE FUNCTION "(-)"(currency)
	RETURNS currency
	AS 'currency', 'currency_uminus'
	LANGUAGE C STRICT IMMUTABLE;

CREATE OPERATOR - (
	rightarg = currency,
	procedure = "(-)"
);

CREATE OR REPLACE FUNCTION "(+)"(currency)
	RETURNS currency
	AS 'currency', 'currency_uplus'
	LANGUAGE C STRICT IMMUTABLE;

CREATE OPERATOR + (
	rightarg = currency,
	procedure = "(+)"
);


--
--	eof
--