Select from dynamic table names

Question

Hello pals,

So here's the problem statement,

SELECT app_label || '_' || model as name from django_content_type where id = 12;

       name        
-------------------
 merc_benz

DJango people might have guessed, 'merc_benz' is a table name in same db. I am writing some complex SQL migrations and I need to select result from such dynamic table names.

How can i use variable name as a table name???

Answer 1

http://www.postgresql.org/docs/8.1/static/ecpg-dynamic.html

The basic answer I think is EXECUTE IMMEDIATE

Answer 2

Something like this...(see RETURN QUERY EXECUTE in the plpgsql portion of the manual)

CREATE function dynamic_table_select(v_id int) returns setof text as $$
DECLARE 
    v_table_name text;
BEGIN
    SELECT app_label || '_' || model into 
    v_table_name from django_content_type where id = v_id;
    RETURN QUERY EXECUTE 'SELECT a_text_column from '||quote_ident(v_table_name);
    RETURN;
END
$$ LANGUAGE plpgsql;

It becomes a little more complex if you want to return more than a single column of one type - either create a TYPE that is representative, or if you're using all the columns of a table, there's already a TYPE of that table name. You could also specify multiple OUT parameters.