Use result from table_schema query as  in select * from Is it possible in PostgreSQL to use the result of the query below as table name in a SELECT * FROM .

SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE 'seam_event_%'
ORDER BY table_name DESC
LIMIT limit 1


The current database is using table partitioning. 90% of the time we only search the last two partition tables. Therefor we would like to create a view with the records of the last 2 partition tables.

I tried the following:

SELECT * FROM (SELECT table_name
    FROM information_schema.tables
    WHERE table_name LIKE 'seam_event_%'
    ORDER BY table_name DESC
    LIMIT 1) AS a
UNION
SELECT * FROM (SELECT table_name
    FROM information_schema.tables
    WHERE table_name LIKE 'seam_event_%'
    ORDER BY table_name DESC
    LIMIT 1 OFFSET 1) AS b


Unfortunatly, this is returning:

table_name
______________
seam_event_74
seam_event_75


Bottom line, which is the best way to query the last two partition tables and how to achieve this?

Thank you in advance.

Regards,

Bas

---------- EDIT ----------

I've tried to create a functions that will return the results of the last two child tables. But i keep getting a syntax error.

Function:

DROP FUNCTION select_from_table();

CREATE OR REPLACE FUNCTION select_from_table()
  RETURNS SETOF seam_event AS
$BODY$
DECLARE
    TABLE_VAR1 varchar;
    TABLE_VAR2 varchar;
BEGIN
    select table_name from information_schema.tables where table_name like 'seam_event_%' order by table_name desc limit 1 INTO TABLE_VAR1;
    select table_name from information_schema.tables where table_name like 'seam_event_%' order by table_name desc limit 1 offset 1 INTO TABLE_VAR2;

    RETURN QUERY EXECUTE 'SELECT * FROM ' || TABLE_VAR1;
END;
$BODY$
  LANGUAGE plpgsql
  COST 100;

SELECT * FROM select_from_table();


ERROR:

ERROR:  syntax error at or near "'SELECT * FROM '"
LINE 1:  EXECUTE 'SELECT * FROM ' ||  $1 
                 ^
QUERY:   EXECUTE 'SELECT * FROM ' ||  $1 
CONTEXT:  SQL statement in PL/PgSQL function "select_from_table" near line 8

********** Error **********

ERROR: syntax error at or near "'SELECT * FROM '"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "select_from_table" near line 8
You can't do this with ordinary SQL, at least in PostgreSQL. You need to use PL/PgSQL to generate dynamic SQL and run it with EXECUTE. The 

RETURN QUERY EXECUTE format('SELECT ... FROM %I WHERE $1, $2', tablename) USING param1, param2;


construct is useful.

See executing dynamic statements.