Function returning table

Question

I want a plpgsql function that returns the content of any table, given the name. The function below, although not working because of many reasons will gove you the general idea. Safety and coding practice aside, what's the easiest way to accomplish this?

In the end I want to get these results trough a Java CallableStatement.

CREATE OR REPLACE FUNCTION get_table(tablename VARCHAR)
RETURNS SETOF RECORD AS $PROC$
BEGIN
 RETURN QUERY SELECT * FROM tablename;
END;
$PROC$ LANGUAGE 'plpgsql';

Answer 1

You can get your function working like this:

CREATE OR REPLACE FUNCTION get_table(tablename VARCHAR)
RETURNS SETOF RECORD AS $PROC$
BEGIN
 RETURN QUERY EXECUTE 'SELECT * FROM ' || quote_ident(tablename);
END;
$PROC$ LANGUAGE 'plpgsql';

In order to call it, you must specify names and data types for all returned columns. If you want to list table "t" which has two columns, you could use your function like this:

SELECT * FROM get_table('t') x(id int, val text);

Which of course, is longer and a lot more trouble than either:

SELECT * FROM t;

or the equivalent:

TABLE t;

I really can't imagine a use-case where such a function makes anything better.

• Trevor Young: Thanks for the info, this is useful in my situation where we have different schemas set up for different users, but the reporting system that we have doesn't allow us to change the search_path properly. This allows the workaround of passing a query into a function, and the function sets the search_path appropriately, then runs the query and returns the result. (And the RETURN QUERY doesn't allow multiple queries or non select queries, helping to keep it safe.)