big-ass number

ban.sql

CREATE TYPE ban;

CREATE FUNCTION banin(cstring, oid, int) RETURNS ban
IMMUTABLE STRICT LANGUAGE internal
AS 'bpcharin';

CREATE FUNCTION banout(ban) RETURNS cstring
IMMUTABLE STRICT LANGUAGE internal
AS 'bpcharout';

CREATE TYPE ban (
    INPUT = banin,
    OUTPUT = banout,
    TYPMOD_IN = bpchartypmodin,
    TYPMOD_OUT = bpchartypmodout,
    LIKE = bpchar,
    STORAGE = plain,
    COLLATABLE = true
);

CREATE FUNCTION baneq(ban, ban) RETURNS bool
IMMUTABLE STRICT LANGUAGE internal
AS 'bpchareq';

CREATE OPERATOR =(
    PROCEDURE = baneq,
    LEFTARG = ban,
    RIGHTARG = ban,
    HASHES
);

CREATE FUNCTION ban(ban, int, boolean) RETURNS ban
IMMUTABLE STRICT LANGUAGE internal
AS 'bpchar';

-- typmod cast
CREATE CAST (ban AS ban)
    WITH FUNCTION ban AS IMPLICIT;

CREATE CAST (int AS ban)
    WITH INOUT AS ASSIGNMENT;

CREATE CAST (ban AS int)
    WITH INOUT;

CREATE FUNCTION hashban(ban) RETURNS int
IMMUTABLE STRICT LANGUAGE SQL
AS $fn$
SELECT $1::int;
$fn$;

CREATE OPERATOR CLASS ban_ops FOR TYPE ban USING hash AS
    OPERATOR 1 =,
    FUNCTION 1 hashban
;

CREATE TABLE foo (a ban(7168));
INSERT INTO foo SELECT i FROM generate_series(1, 5) i;
CREATE TABLE bar (c ban(10));
INSERT INTO bar SELECT i FROM generate_series(3, 7) i;

ANALYZE foo;
ANALYZE bar;

SELECT $$
SELECT a FROM foo JOIN bar ON a = c
$$ AS qry \gset

EXPLAIN :qry;

EXPLAIN (ANALYZE,BUFFERS,VERBOSE)
:qry;