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().