Use result from table_schema query as <table> in select * from <table>

Question

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

• András Váczi: That error cannot come from the code above if I'm not mistaken (I tried it actually and it worked).

Answer 1

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.

• Bas: tried your suggestion but no luck. Can you please assist met by getting my function right.
• Craig Ringer: @user153689 What PostgreSQL version are you on? Maybe you're on an old version before return query supported execute?
• Bas: "PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10)"
• Stolas: @user153689 upgrade your PostGreSQL. As 8.3.7 was release the 19th of March 2009.. However this does not explain your QUERY EXECUTE issue. as this was supported as of 2008 ( See this maillist post postgresql.org/message-id/… ( dated 5th of April 2008 ) )