Function returning table

1
votes

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';
1
plpgsql

1 Answers

3
votes

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.