CODEWITHSUNDEEP
postgresqlstored-procedureswhile-loopschemaplpgsql
TheStranger
TheStranger

Reputation: 1577

How do I make multiple queries in the same stored procedure in PostgreSQL?

I need a stored procedure where I do two things. First I want to get all schema names that is prefixed with 'myschema_', so I've done this:

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE 'myschema_%'

Next, I want a while loop that loops through each schema and gets 'name' from the 'person' table. This means that I somehow have to feed the result from the first select schema statement in as a parameter in the next call. And all of this should be one stored procedure.

This is what I want the stored procedure to return:

|  schema  | name |
-------------------
| schema_1 | Mike |
| schema_1 | Jane |
| schema_2 | Rich |
| schema_3 | Fred |
| schema_4 | Chris|

How do I do this?

Upvotes: 0

Views: 1472

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13009

You would need plpgsql block or procedure and dynamic SQL to do this.

create or replace function my_function()
returns table (sname text, pname text) as
$$
DECLARE
  running_schema text;
  running_name text;
  DYN_SQL constant text default 'select "name" from %I.person';
BEGIN
  for running_schema in --your query 
   SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'myschema_%'
  loop
    for running_name in execute format(DYN_SQL, running_schema) loop
       sname := running_schema;
       pname := running_name;
       return next;
    end loop;
  end loop;
END;
$$ language plpgsql;

-- Unit test
SELECT sname, pname from my_function();

Upvotes: 2

Related Questions