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

Question

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?

In postgresql Procedure will not return any value. You have to write a function for it. — Akhilesh Mishra
– Akhilesh Mishra, Commented May 31, 2021 at 8:46

Stefanov.sm · Accepted Answer · 2021-05-31 08:36:14Z

2

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();

edited May 31, 2021 at 8:36

answered May 31, 2021 at 7:12

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

9 Comments

Laurenz Albe Over a year ago

@a_horse_with_no_name Not if the SELECT list of the query has only a single entry.

TheStranger Over a year ago

When I run this, it says: ERROR: no language specified SQL state: 42P13

Stefanov.sm Over a year ago

My omission. I have editted the answer, $$ language plpgsql; on the last line.

TheStranger Over a year ago

@Stefanov.sm I don't know if there is something I'm misunderstanding here, but I'm still not getting anything out of the stored procedure... It says that it's completed successfully, but I'm not getting the names out... I'm confused.. When I call the stored procedure from she server it says "No results were returned by the query."

Stefanov.sm Over a year ago

Well, this is just the frame of your actual procedure (or function in case you want it to return something). Pls. add your program logic where the comments are. What do you expect to be returned?

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related