How can I access a postgres table by name?

Question

Accessing Tables by Name

I need to reference a table name by a string value given to me. How would I go about doing this in Postgres? Using stored procedures with PLPGSQL is an option, however other languages will be unavailable.

For example:

SELECT count(*) FROM some_function_that_returns_data_by_table_name('mytable');

I have the feeling I did this a long time ago using the pg_XXX tables and a ton of joins, but I can't seem to figure out how to do it now. Does anyone have any ideas?

Although it is possible for me to generate the name at the application level, it would be ideal if I could access the table by name so that the code can be more simple, shared easily between different applications and possibly used in stored procedures.

Answer 1

Why can't you simply do this in 3 steps?

1. invoke the function that gives the name
2. create the SQL with the name
3. execute the SQL

Answer 2

If using PL/pgSQL is acceptable take a look at EXECUTE.