Hello,
I am trying to write a function which takes an argument and uses that argument to return a set of rows from a table with the name given (or possibly inferred) from the argument.
Example
CREATE TABLE test_1 (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE test_2 (id SERIAL PRIMARY KEY, name TEXT, live BOOLEAN, other VARCHAR);
CREATE TABLE test_3 (id SERIAL PRIMARY KEY, name TEXT, number DOUBLE PRECISION);
Now the function in concept would be
function getData(which_table TEXT)
BEGIN
RETURN SELECT * FROM “which_table”
END
Or ideally
function getData(table_num INTEGER)
BEGIN
tblName := test_ || table_num;
RETURN SELECT * FROM test_”which_num”;
END
It’s important to note the 3 tables have a different structure and the case I am trying to cater for is that the user doesn’t know in advance what that table structure is.
I tried
CREATE OR REPLACE FUNCTION getTest(mytable TEXT)
RETURNS SETOF RECORD AS
$$
DECLARE
result mytable%rowtype;
BEGIN
FOR result IN SELECT * FROM mytable
LOOP
RETURN NEXT result;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
Obviously mytable is not a real table I would like to use a table whose name is specified in the variable mytable.
Does anyone have any advice if this is even possible and what I should be looking at to get there or to do instead?
Thanks in advance for any possible help
--
Jonathan Harden
Zen Internet Ltd