Re: domain cast in parameterized vs. non-parameterized query

Hi

2017-12-20 23:41 GMT+01:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> I wrote:
> > You might consider whether you can write 'spa-000'::uid explicitly in
> your
> > query; that results in immediate application of the domain coercion, so
> > that the planner no longer sees that as a run-time operation it has to
> > avoid.
>
> Hm, scratch that --- experimentation shows that the parser still produces
> a CoerceToDomain node in that case, not a literal of the domain type.
>
> regression=# create domain foo as text;
> CREATE DOMAIN
> regression=# explain verbose select 'x'::foo;
> QUERY PLAN
> -------------------------------------------
> Result (cost=0.00..0.01 rows=1 width=32)
> Output: ('x'::text)::foo
> (2 rows)
>
> You could force the issue with an immutable function:
>

Why the rewrite doesn't reduce it? Or why parser does it?

Regards

Pavel

>
> regression=# create function forcefoo(text) returns foo as
> regression-# 'begin return $1::foo; end' language plpgsql immutable;
> CREATE FUNCTION
> regression=# explain verbose select forcefoo('x');
> QUERY PLAN
> -------------------------------------------
> Result (cost=0.00..0.01 rows=1 width=32)
> Output: 'x'::foo
> (2 rows)
>
> Marking this function as immutable is sort of a lie, because it
> is effectively telling the planner that you don't expect any
> failure from pre-evaluation of the function. But it'd get the
> job done, and in most situations there's no practical difference
> because any failure would have happened anyway at runtime.
>

> regards, tom lane
>
>