Thread: Functions which use an argument to decide which table to read
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
In response to Jonathan Harden : > 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. You have to use EXECUTE. Build a string that contains your select, and execute that string. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
"Jonathan Harden" <jonathan.harden@zeninternet.co.uk> writes:
> 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.
You'd need to construct the SELECT command as a string and then EXECUTE
it.
Your other problem is that you don't know what row type the function
is going to return, and there's no really nice way around that ---
you could try declaring it as RETURNS SETOF RECORD, but then you'd
have to have every calling query specify what type the result is,
and that's never any fun.
On the whole, this suggests to me that you've got a bad database schema
design and you need to reconsider things at that level. I think what
you're trying to do is effectively a form of EAV
(entity-attribute-value) which just does not play very nicely with SQL
databases. It's usually better to take the time up-front to nail down a
better-specified model of your data. If you can't, a SQL database may
not be the tool to be using.
regards, tom lane