Re: Does Type Have = Operator?

On May 10, 2016, at 6:14 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Given that you're coercing both one input value and the result to text,
> I don't understand why you don't just compare the text representations.

Because sometimes the text is not equal when the casted text is. Consider

'foo'::citext = 'FOO':citext

> I'm also not very clear on what you mean by "comparing column defaults".
> A column default is an expression (in the general case anyway), not just
> a value of the type.

Yeah, the pgTAP column_default_is() function takes a string representation of an expression.

> Maybe if you'd shown us the is() function, as well as a typical usage
> of _def_is(), this would be less opaque.

Here’s is():

CREATE OR REPLACE FUNCTION is (anyelement, anyelement, text)
RETURNS TEXT AS $$
DECLARE
result BOOLEAN;
output TEXT;
BEGIN
-- Would prefer $1 IS NOT DISTINCT FROM, but that's not supported by 8.1.
result := NOT $1 IS DISTINCT FROM $2;
output := ok( result, $3 );
RETURN output || CASE result WHEN TRUE THEN '' ELSE E'\n' || diag(
' have: ' || CASE WHEN $1 IS NULL THEN 'NULL' ELSE $1::text END ||
E'\n want: ' || CASE WHEN $2 IS NULL THEN 'NULL' ELSE $2::text END
) END;
END;
$$ LANGUAGE plpgsql;

_def_is() is called by another function, which effectively is:

CREATE OR REPLACE FUNCTION _cdi ( NAME, NAME, NAME, anyelement, TEXT )
RETURNS TEXT AS $$
BEGIN

RETURN _def_is(
pg_catalog.pg_get_expr(d.adbin, d.adrelid),
pg_catalog.format_type(a.atttypid, a.atttypmod),
$4, $5
)
FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c, pg_catalog.pg_attribute a,
pg_catalog.pg_attrdef d
WHERE n.oid = c.relnamespace
AND c.oid = a.attrelid
AND a.atthasdef
AND a.attrelid = d.adrelid
AND a.attnum = d.adnum
AND n.nspname = $1
AND c.relname = $2
AND a.attnum > 0
AND NOT a.attisdropped
AND a.attname = $3;
END;
$$ LANGUAGE plpgsql;

That function si called like this:

_cdi( :schema, :table, :column, :default, :description );

Best,

David