PostgreSQL - Combine Queries

Question

I have a query that I want to combine with another query but without success.

The first query returns all schema names which have the table named status:

select s.schema_name 
from   information_schema.schemata s 
where exists (select * 
              from information_schema.tables t 
              where t.table_schema = s.schema_name 
              and t.table_name = 'status')

In each status table is a column lastLogin which can be selected like this:

select "lastLogin" 
from   "someID".status 
where  "lastLogin" is not null

How can I get all values for lastLogin from all status tables from every schema?

Thanks in advance, Doobie

Laurenz Albe · Accepted Answer · 2018-02-27 12:41:12Z

1

You cannot do that, since a schema name in an SQL query must be a constant value. You will have to construct the second query based on the result of the first query.

What you could do is construct the second query so that it uses UNION to query all tables called status in a single query.

answered Feb 27, 2018 at 12:41

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DoobieAsDave Over a year ago

Thank you for your answer and expertise Mr. Albe!

clemens · Accepted Answer · 2018-02-28 06:33:37Z

You cannot create a single query for the general problem where the number of schemes is unknown. But you may create a stored procedure for that. I assume lastLogin has the type TIMESTAMP.

CREATE FUNCTION list_last_logins() RETURNS SETOF TIMESTAMP AS $$
DECLARE
    the_row RECORD;
    the_statement TEXT;
    the_result RECORD;
BEGIN
    FOR the_row IN SELECT t.schema_name FROM information_schema.tables t WHERE t.table_name = 'status'
    LOOP
        the_statement := FORMAT('SELECT "lastLogin" FROM %s.status WHERE "lastLogin" IS NOT NULL', the_row.schema_name);
        FOR the_result IN EXECUTE the_statement
        LOOP
            RETURN the_result."lastLogin";
        END LOOP;
    END LOOP;
    RETURN;
END;
$_$ LANGUAGE plpgsql;

Since Postgres 9.5 the inner loop may be shortened to:

CREATE FUNCTION list_last_logins() RETURNS SETOF TIMESTAMP AS $$
DECLARE
    the_row RECORD;
    the_statement TEXT;
BEGIN
    FOR the_row IN SELECT t.schema_name FROM information_schema.tables t WHERE t.table_name = 'status'
    LOOP
        the_statement := FORMAT('SELECT "lastLogin" FROM %s.status WHERE "lastLogin" IS NOT NULL', the_row.schema_name);
        RETURN QUERY EXECUTE the_statement;
    END LOOP;
    RETURN;
END;
$_$ LANGUAGE plpgsql;

You can now select all values with

SELECT * FROM list_last_logins();

Collectives™ on Stack Overflow

PostgreSQL - Combine Queries

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related