Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
That error cannot come from the code above if I'm not mistaken (I tried it actually and it worked). –  dezso Feb 12 at 13:33
add comment

1 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.

share|improve this answer
    
tried your suggestion but no luck. Can you please assist met by getting my function right.; –  user153689 Feb 12 at 9:28
1  
@user153689 What PostgreSQL version are you on? Maybe you're on an old version before return query supported execute? –  Craig Ringer Feb 13 at 0:01
    
"PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10)" –  user153689 Feb 17 at 10:01
    
@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 ) ) –  Stolas Feb 18 at 9:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.