-- Helper functions to deal with cases where binary-coercible matches are

-- allowed.

-- This should match IsBinaryCoercible() in parse_coerce.c.

-- It doesn't currently know about some cases, notably domains, anyelement,

-- anynonarray, anyenum, or record, but it doesn't need to (yet).

create function binary_coercible(oid, oid) returns bool as $$

begin

if $1 = $2 then return true; end if;

return false;

end

$$ language plpgsql strict stable;

-- This one ignores castcontext, so it considers only physical equivalence

-- and not whether the coercion can be invoked implicitly.

create function physically_coercible(oid, oid) returns bool as $$

-- This one ignores castcontext, so it will allow cases where an explicit

-- (but still binary) cast would be required to convert the input type.

-- We don't currently use this for any tests in this file, but it is a

-- reasonable alternative definition for some scenarios.

create function explicitly_binary_coercible(oid, oid) returns bool as $$

begin

if $1 = $2 then return true; end if;

if EXISTS(select 1 from pg_catalog.pg_cast where

-- Check that operators' underlying functions have suitable comments,

-- namely 'implementation of XXX operator'. (Note: it's not necessary to

-- put such comments into pg_proc.h; initdb will generate them as needed.)

-- put such comments into pg_proc.dat; initdb will generate them as needed.)

-- In some cases involving legacy names for operators, there are multiple

-- operators referencing the same pg_proc entry, so ignore operators whose

-- comments say they are deprecated.

(0 rows)

-- Cross-check transfn against its entry in pg_proc.

-- NOTE: use physically_coercible here, not binary_coercible, because

SELECT a.aggfnoid::oid, p.proname, ptr.oid, ptr.proname

FROM pg_aggregate AS a, pg_proc AS p, pg_proc AS ptr

WHERE a.aggfnoid = p.oid AND

OR NOT (ptr.pronargs =

CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1

ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END)

OR NOTphysically_coercible(ptr.prorettype, a.aggtranstype)

OR NOTphysically_coercible(a.aggtranstype, ptr.proargtypes[0])

OR NOTbinary_coercible(ptr.prorettype, a.aggtranstype)

OR NOTbinary_coercible(a.aggtranstype, ptr.proargtypes[0])

OR (p.pronargs > 0 AND

NOTphysically_coercible(p.proargtypes[0], ptr.proargtypes[1]))

NOTbinary_coercible(p.proargtypes[0], ptr.proargtypes[1]))

OR (p.pronargs > 1 AND

NOTphysically_coercible(p.proargtypes[1], ptr.proargtypes[2]))

NOTbinary_coercible(p.proargtypes[1], ptr.proargtypes[2]))

OR (p.pronargs > 2 AND

NOTphysically_coercible(p.proargtypes[2], ptr.proargtypes[3]))

NOTbinary_coercible(p.proargtypes[2], ptr.proargtypes[3]))

-- we could carry the check further, but 3 args is enough for now

OR (p.pronargs > 3)

);

aggfnoid | proname | oid | proname

----------+---------+-----+---------

NOT binary_coercible(p.proargtypes[1], pfn.proargtypes[2]))

OR (pfn.pronargs > 3 AND

NOT binary_coercible(p.proargtypes[2], pfn.proargtypes[3]))

-- we could carry the check further, but 3 args is enough for now

-- we could carry the check further, but 4 args is enough for now

OR (pfn.pronargs > 4)

);

aggfnoid | proname | oid | proname

----------+---------+-----+---------

OR NOT (ptr.pronargs =

CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1

ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END)

OR NOTphysically_coercible(ptr.prorettype, a.aggmtranstype)

OR NOTphysically_coercible(a.aggmtranstype, ptr.proargtypes[0])

OR NOTbinary_coercible(ptr.prorettype, a.aggmtranstype)

OR NOTbinary_coercible(a.aggmtranstype, ptr.proargtypes[0])

OR (p.pronargs > 0 AND

NOTphysically_coercible(p.proargtypes[0], ptr.proargtypes[1]))

NOTbinary_coercible(p.proargtypes[0], ptr.proargtypes[1]))

OR (p.pronargs > 1 AND

NOTphysically_coercible(p.proargtypes[1], ptr.proargtypes[2]))

NOTbinary_coercible(p.proargtypes[1], ptr.proargtypes[2]))

OR (p.pronargs > 2 AND

NOTphysically_coercible(p.proargtypes[2], ptr.proargtypes[3]))

NOTbinary_coercible(p.proargtypes[2], ptr.proargtypes[3]))

-- we could carry the check further, but 3 args is enough for now

OR (p.pronargs > 3)

);

aggfnoid | proname | oid | proname

----------+---------+-----+---------

OR NOT (ptr.pronargs =

CASE WHEN a.aggkind = 'n' THEN p.pronargs + 1

ELSE greatest(p.pronargs - a.aggnumdirectargs, 1) + 1 END)

OR NOTphysically_coercible(ptr.prorettype, a.aggmtranstype)

OR NOTphysically_coercible(a.aggmtranstype, ptr.proargtypes[0])

OR NOTbinary_coercible(ptr.prorettype, a.aggmtranstype)

OR NOTbinary_coercible(a.aggmtranstype, ptr.proargtypes[0])

OR (p.pronargs > 0 AND

NOTphysically_coercible(p.proargtypes[0], ptr.proargtypes[1]))

NOTbinary_coercible(p.proargtypes[0], ptr.proargtypes[1]))

OR (p.pronargs > 1 AND

NOTphysically_coercible(p.proargtypes[1], ptr.proargtypes[2]))

NOTbinary_coercible(p.proargtypes[1], ptr.proargtypes[2]))

OR (p.pronargs > 2 AND

NOTphysically_coercible(p.proargtypes[2], ptr.proargtypes[3]))

NOTbinary_coercible(p.proargtypes[2], ptr.proargtypes[3]))

-- we could carry the check further, but 3 args is enough for now

OR (p.pronargs > 3)

);

aggfnoid | proname | oid | proname

----------+---------+-----+---------

NOT binary_coercible(p.proargtypes[1], pfn.proargtypes[2]))

OR (pfn.pronargs > 3 AND

NOT binary_coercible(p.proargtypes[2], pfn.proargtypes[3]))

-- we could carry the check further, but 3 args is enough for now

-- we could carry the check further, but 4 args is enough for now

OR (pfn.pronargs > 4)

);

aggfnoid | proname | oid | proname

----------+---------+-----+---------

-- Check that all combine functions have signature

-- combine(transtype, transtype) returns transtype

-- NOTE: use physically_coercible here, not binary_coercible, because

SELECT a.aggfnoid, p.proname

FROM pg_aggregate as a, pg_proc as p

WHERE a.aggcombinefn = p.oid AND

(p.pronargs != 2 OR

p.prorettype != p.proargtypes[0] OR

p.prorettype != p.proargtypes[1] OR

NOTphysically_coercible(a.aggtranstype, p.proargtypes[0]));

NOTbinary_coercible(a.aggtranstype, p.proargtypes[0]));

aggfnoid | proname

----------+---------

(0 rows)

createfunctionbinary_coercible(oid,oid) returns boolas $$

if $1= $2 then return true; end if;

end

$$ language plpgsql strict stable;

createfunctionphysically_coercible(oid,oid) returns boolas $$

createfunctionexplicitly_binary_coercible(oid,oid) returns boolas $$

if $1= $2 then return true; end if;

if EXISTS(select1frompg_catalog.pg_castwhere

a.aggfinalfn=0ANDp.prorettype!=a.aggtranstype;

SELECTa.aggfnoid::oid,p.proname,ptr.oid,ptr.proname

FROM pg_aggregateAS a, pg_procAS p, pg_procAS ptr

WHEREa.aggfnoid=p.oidAND

OR NOT (ptr.pronargs=

CASE WHENa.aggkind='n' THENp.pronargs+1

ELSE greatest(p.pronargs-a.aggnumdirectargs,1)+1 END)

OR NOTphysically_coercible(ptr.prorettype,a.aggtranstype)

OR NOTphysically_coercible(a.aggtranstype,ptr.proargtypes[0])

OR NOTbinary_coercible(ptr.prorettype,a.aggtranstype)

OR NOTbinary_coercible(a.aggtranstype,ptr.proargtypes[0])

OR (p.pronargs>0AND

NOTphysically_coercible(p.proargtypes[0],ptr.proargtypes[1]))

NOTbinary_coercible(p.proargtypes[0],ptr.proargtypes[1]))

OR (p.pronargs>1AND

NOTphysically_coercible(p.proargtypes[1],ptr.proargtypes[2]))

NOTbinary_coercible(p.proargtypes[1],ptr.proargtypes[2]))

OR (p.pronargs>2AND

NOTphysically_coercible(p.proargtypes[2],ptr.proargtypes[3]))

NOTbinary_coercible(p.proargtypes[2],ptr.proargtypes[3]))

OR (p.pronargs>3)

);

NOT binary_coercible(p.proargtypes[1],pfn.proargtypes[2]))

OR (pfn.pronargs>3AND

NOT binary_coercible(p.proargtypes[2],pfn.proargtypes[3]))

OR (pfn.pronargs>4)

);

OR NOT (ptr.pronargs=

CASE WHENa.aggkind='n' THENp.pronargs+1

ELSE greatest(p.pronargs-a.aggnumdirectargs,1)+1 END)

OR NOTphysically_coercible(ptr.prorettype,a.aggmtranstype)

OR NOTphysically_coercible(a.aggmtranstype,ptr.proargtypes[0])

OR NOTbinary_coercible(ptr.prorettype,a.aggmtranstype)

OR NOTbinary_coercible(a.aggmtranstype,ptr.proargtypes[0])

OR (p.pronargs>0AND

NOTphysically_coercible(p.proargtypes[0],ptr.proargtypes[1]))

NOTbinary_coercible(p.proargtypes[0],ptr.proargtypes[1]))

OR (p.pronargs>1AND

NOTphysically_coercible(p.proargtypes[1],ptr.proargtypes[2]))

NOTbinary_coercible(p.proargtypes[1],ptr.proargtypes[2]))

OR (p.pronargs>2AND

NOTphysically_coercible(p.proargtypes[2],ptr.proargtypes[3]))

NOTbinary_coercible(p.proargtypes[2],ptr.proargtypes[3]))

OR (p.pronargs>3)

);

OR NOT (ptr.pronargs=

CASE WHENa.aggkind='n' THENp.pronargs+1

ELSE greatest(p.pronargs-a.aggnumdirectargs,1)+1 END)

OR NOTphysically_coercible(ptr.prorettype,a.aggmtranstype)

OR NOTphysically_coercible(a.aggmtranstype,ptr.proargtypes[0])

OR NOTbinary_coercible(ptr.prorettype,a.aggmtranstype)

OR NOTbinary_coercible(a.aggmtranstype,ptr.proargtypes[0])

OR (p.pronargs>0AND

NOTphysically_coercible(p.proargtypes[0],ptr.proargtypes[1]))

NOTbinary_coercible(p.proargtypes[0],ptr.proargtypes[1]))

OR (p.pronargs>1AND

NOTphysically_coercible(p.proargtypes[1],ptr.proargtypes[2]))

NOTbinary_coercible(p.proargtypes[1],ptr.proargtypes[2]))

OR (p.pronargs>2AND

NOTphysically_coercible(p.proargtypes[2],ptr.proargtypes[3]))

NOTbinary_coercible(p.proargtypes[2],ptr.proargtypes[3]))

OR (p.pronargs>3)

);

NOT binary_coercible(p.proargtypes[1],pfn.proargtypes[2]))

OR (pfn.pronargs>3AND

NOT binary_coercible(p.proargtypes[2],pfn.proargtypes[3]))

OR (pfn.pronargs>4)

);

SELECTa.aggfnoid,p.proname

FROM pg_aggregateas a, pg_procas p

WHEREa.aggcombinefn=p.oidAND

(p.pronargs!=2OR

p.prorettype!=p.proargtypes[0]OR

p.prorettype!=p.proargtypes[1]OR

NOTphysically_coercible(a.aggtranstype,p.proargtypes[0]));

NOTbinary_coercible(a.aggtranstype,p.proargtypes[0]));