Using a function for the table name

Question

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.

András Váczi · Accepted Answer · 2012-12-11 14:39:11Z

2

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";

edited Dec 11, 2012 at 14:39

András Váczi

3,02225 silver badges30 bronze badges

answered Dec 11, 2012 at 12:18

Wojtas

2,3641 gold badge17 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

qqx Over a year ago

I agree that using inheritance for this would be the way to go. But, when doing that you'll want to turn on the constraint_exclusion setting in postgresql.conf.

András Váczi Over a year ago

@qqx - to be precise, constraint_exclusion should be set to partition

qqx Over a year ago

It should be set to at least partition. on would also work, but may cause other queries to be slower. But I now see that partition is the default value for 9.1, that's changed since I last used partitioning.

James Baker Over a year ago

I used inheritance in the end. I don't think execute would have worked because, and admittedly I didn't mention this, the SELECT was a nested SELECT.

Collectives™ on Stack Overflow

Using a function for the table name

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related