Postgresql – Use result from table_schema query as in select * from

information-schemapostgresql

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

Best Answer

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.

Related Solutions

How to Create Select Query Function in PostgreSQL

Function Execute like this And get my Answer

select * from functionname();

PostgreSQL 9.2 – How to Automate COPY Command

The scripting answers above will obviously work.

But, assuming you have to, have to, have it executed from within the DB (let us know why :) ), the code below (minus the loop, etc.) should get you on your way.

You were very close btw.

CREATE or REPLACE FUNCTION function_1(rows text)
RETURNS void AS $$
begin
  execute 'COPY (
    SELECT * 
      FROM backup_table
     WHERE id = ' || rows || ' 
       AND status = 1
  ) TO ''/var/lib/pgsql/'||rows||'.dat''';
end
$$ language 'plpgsql';

EDIT 1: function loop example and formatting

CREATE or REPLACE FUNCTION function_loop(rows_arr text[])
RETURNS void AS $$
declare
  crtRow text;
begin
  ForEach crtRow in array rows_arr
  LOOP
     perform function_1(crtRow);  
  end loop;
end
$$ language 'plpgsql';

Executed as

select function_loop('{1,2,3}');

Please note there are no checks being performed here.

E.g. This will create a zero byte 3.dat even if there is no id=3 in the table

EDIT 2: A more extensible option for thousands of id would be

CREATE or REPLACE FUNCTION function_loop1(startId int, endId int)
RETURNS void AS $$
declare
  allIds text[];
  crtRow text;
begin
  select array_agg(id::text) into allIds 
    from backup_table
   where status=1 
     and id between startId and endId;
  ForEach crtRow in array allIds
  LOOP
    perform function_1(crtRow);  
  end loop;
end
$$ language 'plpgsql';