usecase_exec_using.sql

-- drop table foo cascade;
create table foo (
  uid char(32) primary key,
  flag boolean
);
insert into foo  (
  WITH users AS (SELECT generate_series(0, 1000)::INTEGER AS id)
  SELECT md5(id::TEXT)::CHAR(32) AS uid, true as flag FROM users
);

CREATE
TYPE t_tel_flag as (
  tel text,
  flag boolean
);

CREATE OR REPLACE
FUNCTION get_flag(p_uid TEXT, p_tel TEXT)
    RETURNS t_tel_flag
    LANGUAGE plpgsql STABLE SECURITY DEFINER
    AS $_$
DECLARE
    l_rows integer;
    l_partname TEXT;
    r t_tel_flag%rowtype;
BEGIN

    l_partname := 'foo';
    r.tel  := p_tel;
    r.flag := NULL;

    -- Lookup in the current data table
    EXECUTE 'SELECT flag FROM '
            || l_partname::regclass
            || ' WHERE uid = ' || quote_literal(p_uid)
    INTO r.flag;

    RETURN r;

END;
$_$;

CREATE OR REPLACE
FUNCTION get_flag_using(p_uid TEXT, p_tel TEXT)
    RETURNS t_tel_flag
    LANGUAGE plpgsql STABLE SECURITY DEFINER
    AS $_$
DECLARE
    l_rows integer;
    l_partname TEXT;
    r t_tel_flag%rowtype;
BEGIN

    l_partname := 'foo';
    r.tel  := p_tel;
    r.flag := NULL;

    -- Lookup in the current data table
    EXECUTE 'SELECT flag FROM '
            || l_partname::regclass
            || ' WHERE uid = $1'
    INTO r.flag
    USING p_uid;

    RETURN r;

END;
$_$;

-- ANALYZE or not
-- Tests
-- \x
select * from pg_stat_user_tables  where relname = 'foo' ;
select * from get_flag('cfcd208495d565ef66e7dff9f98764da','A');
select * from pg_stat_user_tables  where relname = 'foo' ;
select * from get_flag_using('cfcd208495d565ef66e7dff9f98764da','A');
select * from pg_stat_user_tables  where relname = 'foo' ;