I have a timescale db with multiple tables having the same structure.

I want to retrieve the recent row from each table where a value is true.

My logic is to

1. retrieve all the tablenames for the tables where this condition can be true
2. loop over list of tablenames and select the rows where the condition is met

I get an syntax error on the FOR loop but I expect that I do more things wrong.

Can someone suggest a solution please? Thank you in advance.

DECLARE
tablename text;
BEGIN
    FOR tablename IN
    SELECT table_name FROM information_schema.tables
    WHERE table_name LIKE 'ohlc%'
    LOOP
      SELECT WHERE tablename.is_active is TRUE
      ORDER BY time_stamp DESC
      Limit 1
    END LOOP;
END;

CodePudding user response：

translate your problem

1. find table that have specific column name in schema. How to find a table having a specific column in postgresql
2. first condition meet then loop. Function to loop through and select data from multiple tables
3. most tricky issue is quote_ident.

create or replace function test0()
returns table (_is_active boolean, id int) as
$$
declare tbl text;
begin
for tbl in
    select quote_ident( table_name)
    from information_schema.columns
    where table_schema = 'public'
    and table_name ilike 'ohlc%'
    and column_name = 'is_active'
    loop
        return query EXECUTE
        'select ' || quote_ident('is_active') || ' , ' || quote_ident('id') || ' from ' || tbl || ' where '|| quote_ident('is_active') ||' is true';
    end loop;
end
$$ language plpgsql;