Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE specializes (eid int, name text);
INSERT INTO specializes VALUES (1, 'foo');

CREATE TABLE



INSERT 0 1


CREATE OR REPLACE FUNCTION is_instructor_specialized_in(eid INT, course_area VARCHAR(50))
RETURNS BOOLEAN AS $$
  SELECT EXISTS(SELECT 1 FROM Specializes s WHERE s.eid = eid AND s.name = course_area);
$$ LANGUAGE sql;

CREATE FUNCTION


-- "should" return false, but returns true:
SELECT is_instructor_specialized_in(666, 'foo') AS expect_false;


expect_false


t



SELECT 1


-- The same as plpgsql function:
CREATE OR REPLACE FUNCTION func_plpgsql(eid INT, course_area text)
  RETURNS BOOLEAN
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN EXISTS(SELECT FROM specializes s WHERE s.eid = eid AND s.name = course_area);
END
$func$;

CREATE FUNCTION


-- "should" return false, but raises an error (luckily):
SELECT func_plpgsql(666, 'foo') AS expect_false;

ERROR:  column reference "eid" is ambiguous
LINE 1: ...CT EXISTS(SELECT FROM specializes s WHERE s.eid = eid AND s....
                                                             ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT EXISTS(SELECT FROM specializes s WHERE s.eid = eid AND s.name = course_area)
CONTEXT:  PL/pgSQL function func_plpgsql(integer,text) line 3 at RETURN


--- proper function with unambiguous names:
CREATE OR REPLACE FUNCTION func_proper(_eid int, _course_area text)
  RETURNS boolean
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT EXISTS(SELECT FROM specializes s WHERE s.eid = _eid AND s.name = _course_area);
$func$;

CREATE FUNCTION


CREATE OR REPLACE FUNCTION func_proper2(_eid int, _course_area text)
  RETURNS boolean
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT EXISTS(SELECT FROM specializes s WHERE s.eid = $1 AND s.name = $2);
$func$;

CREATE FUNCTION


SELECT func_proper(666, 'foo') AS f1_expect_false
     , func_proper(1, 'foo') AS f1_expect_true
     , func_proper2(666, 'foo') AS f2_expect_false
     , func_proper2(1, 'foo') AS f2_expect_true;


f1_expect_false
f1_expect_true
f2_expect_false
f2_expect_true


f
t
f
t



SELECT 1