Functions which use an argument to decide which table to read

From: "Jonathan Harden" <jonathan(dot)harden(at)zeninternet(dot)co(dot)uk>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Functions which use an argument to decide which table to read
Date: 2009-10-09 11:56:00
Message-ID: 000001ca48d7$78bc5f20$6a351d60$@harden@zeninternet.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message A. Kretschmer 2009-10-09 12:23:29 Re: Functions which use an argument to decide which table to read
Previous Message Luiz Eduardo Cantanhede Neri 2009-10-09 10:58:58 Re: /Var Partition Full - How can a change PGDATA?