planner issue with constraint exclusion

Hello,

I ran into this problem recently:

https://projects.commandprompt.com/public/replicator/pastebin?show=f1288d4d8%0D

Of the functions the only one that will use constraint_exclusion is the
one that explicitly passes the date value. I kind of get why except for
the one that uses EXECUTE. As EXECUTE has to replan the query, shouldn't
it be able to use constraint_exclusion?

(text also below for those that don't want to fire up a browser)

CREATE OR REPLACE FUNCTION test_search1() RETURNS integer AS $$ DECLARE   temp date;   tmp integer; BEGIN   SELECT
date(timehit)INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1;   SELECT l.unit_id FROM INTO tmp foo_stats_day lsd, foo51
lWHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = date(timehit);   RETURN tmp; END
 
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION test_search2() RETURNS integer AS $$ DECLARE   temp date;   tmp integer; BEGIN   SELECT
date(timehit)INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1;   SELECT l.unit_id FROM INTO tmp foo_stats_day lsd, foo51
lWHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = '2006-07-17';   RETURN tmp; END
 
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION test_search3() RETURNS integer AS $$ DECLARE   temp date;   tmp integer; BEGIN   SELECT
date(timehit)INTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1;   SELECT l.unit_id FROM INTO tmp foo_stats_day lsd, foo51
lWHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = temp;   RETURN tmp; END
 
$$ LANGUAGE plpgsql STABLE;

CREATE OR REPLACE FUNCTION test_search4() RETURNS integer AS $$
use strict;
use warnings;
my $sql = "SELECT date(timehit) AS timehit FROM foo51 WHERE unit_id = 1 LIMIT 1";
my $rv = spi_exec_query($sql);
return undef if( ! defined $rv->{rows}[0]->{'timehit'} );
my $date = $rv->{rows}[0]->{'timehit'};

$sql = "SELECT l.unit_id FROM foo_stats_day lsd, foo51 l WHERE lsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day
='$date'";
 
$rv = spi_exec_query($sql);

return undef if( ! defined $rv->{rows}[0]->{'unit_id'} );
my $unit_id = $rv->{rows}[0]->{'unit_id'};

return $unit_id;
$$ LANGUAGE 'plperlu' STABLE;

CREATE OR REPLACE FUNCTION test_search5() RETURNS integer AS $$ DECLARE   temp date;   tmp integer; BEGIN   SELECT
timehitINTO temp FROM foo51 WHERE unit_id = 1 LIMIT 1;   EXECUTE 'SELECT l.unit_id FROM foo_stats_day lsd, foo51 l
WHERElsd.a_serv_id = l.unit_id AND lsd.a_serv_id = 1 AND day = ''' || temp || '''';   RETURN 1; END
 
$$ LANGUAGE plpgsql STABLE;


-- 
PostgreSQL  Consulting, Development, Support, Training  503-667-4564 - http://www.commandprompt.com/  The PostgreSQL
Company,serving since 1997