Assuming current version PostgreSQL 9.2 for lack of information.
Plain SQL
The simple query could look like this:
SELECT max(buildid)
FROM app_build
WHERE buildid !~ '\d+_\d+_\d+_\d+$' -- to exclude old format
GROUP BY substring(buildid, '^[^_]+')
ORDER BY substring(buildid, '^[^_]+');
The WHERE condition used a regular expression:
buildid !~ '\d+_\d+_\d+_\d+$'
Excludes buildid that end in 4 integer numbers divided by _.
\d .. character class shorthand for digits. Only one backslash \ in modern PostgreSQL with standard_conforming_strings = ON.
+ .. 1 or more of preceding atom.
$ .. As last character: anchored to the end of the string.
There may be a cheaper / more accurate way, you did not properly specify the format.
GROUP BY and ORDER BY extract the the string before the first occurrence of _ with substring() as app name to group and order by. The regexp explained:
^ .. As first character: anchor search expression to start of string.
[^_] .. Character class: any chracter that is not _.
Does the same as split_part(buildid, '_', 1). But split_part() may be faster ..
Function
If you want to write a function where the table name is variable, you need dynamic SQL. That is a plpgsql function with EXECUTE:
CREATE OR REPLACE FUNCTION getmax(_tbl regclass)
RETURNS SETOF text AS
$func$
BEGIN
RETURN QUERY
EXECUTE format($$
SELECT max(buildid)
FROM %s
WHERE buildid !~ '\d+_\d+_\d+_\d+$'
GROUP BY substring(buildid, '^[^_]+')
ORDER BY substring(buildid, '^[^_]+')$$, _tbl);
END
$func$ LANGUAGE plpgsql;
Call:
SELECT * FROM getmax('app_build');
Or if you are, in fact, using mixed case identifiers:
SELECT * FROM getmax('"App_build"');
->SQLfiddle demo.
More info on the object identifier class regclass in this related questions:
Table name as a PostgreSQL function parameter
