Select from dynamic table in function without using EXECUTE

You can't avoid execute here, per igor's comment. The best alternative is to create a function that creates a function, which you can then call as needed. Example:

create function gen_select_server(p_id text)
  returns boolean
as $gen$
begin
  execute $exec$
  create function $exec$ || quote_ident('select_server_' || p_id) || $exec$
    returns integer
  as $body$
  begin
    return serialnum
    from $exec$ || quote_ident('tbl_' || p_id) || $exec$
    where id = $exec$ || quote_literal('id_' || p_id) || $exec$;
  end;
  $body$ language plpgsql;
  $exec$;
  return true;
end;
$gen$ language plpgsql;

-- usage:
select gen_select_server('1234');   -- call this only once; uses execute
select * from select_server_1234(); -- no execute here

As the above highlights, it can get messy with string delimiters. Also note that the above isn't your original function -- it's primarily to illustrate how to quote things properly within the big $exec$ "string" block.

I'd recommend to stick to using execute, that being said. Or using an ORM, for that matter. Maintaining these micro-optimizations is potentially a pain worse than the superficial gain in performance.

You cannot execute dynamic SQL without EXECUTE in PL/pgSQL. That's what makes it dynamic in the first place.

But you can streamline your function quite a bit:

CREATE OR REPLACE FUNCTION select_server(p_id text, OUT serial_value integer)
 LANGUAGE plpgsql STRICT AS
$func$ 
BEGIN
   EXECUTE 'SELECT serialnum FROM tbl' || left($1::text, 4)) || ' WHERE id = $1'
   USING $1
   INTO  serial_value;
END
$func$;

• Normally you would have to sanitize any identifier.
  But you do not need quote_ident() in this special case, since the only dynamic component are 4 digits from an integer. Neither SQL injection nor illegal identifiers are possible.

• Pass the value of p_id as value using the USING clause.

• Reduce the number of assignments. Those are comparatively expensive in PL/pgSQL. You only need a single SQL statement to do everything.

• The OUT parameter helps to shorten the syntax.

• I also made the function STRICT (RETURNS NULL ON NULL INPUT), since it would not make sense with NULL as input.

• left() is slightly faster than substr().