CODEWITHSUNDEEP
sqldatabasefunctionpostgresql
James Baker
James Baker

Reputation: 1256

Using a function for the table name

I have a set of 4 tables that contain different partitions of some data. I also have a plpgsql function that will take an id and return the name of the table containing that data as a 'character varying'.

However, when I try to use this function to select from the correct table, e.g.

SELECT f.a FROM getTable(someID) AS f;

it doesn't seem to work. It doesn't throw an error on the SELECT, but it doesn't return the fields I'm expecting either (i.e. it says f.a doesn't exist).

How do I select data from a table where the table name is given by the return of a function?

I'm using PostgreSQL 9.1. This is going to be run over millions of records, so I don't want to have to do it as two separate calls.

Upvotes: 0

Views: 398

Answers (1)

Wojtas
Wojtas

Reputation: 2354

I think for the problem described in the topic you should use inheritance with tables.

Answering the question - use execute:

execute "SELECT f.a FROM " || getTable(someID) || " AS f";

Upvotes: 2

Related Questions