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