How to iterate over SELECT query results in PL/pgSQL?

Question

I'm trying run a SELECT query and then for each of the results I want to print something in the console. However, I get no results, and I have no idea why. If I run the SELECT on its own I do get table names.

create or replace function test()
RETURNS void AS
$body$
  DECLARE
      elem text;
  begin
  FOR elem IN
    SELECT table_name FROM information_schema.tables WHERE table_name ~ 'api_configuration_'
    LOOP
      raise notice 'Table name is: %', elem;
    END LOOP;
  end;
$body$
LANGUAGE plpgsql

I want to print each table_name found by the query

`SELECT table_name FROM information_schema.tables WHERE table_name ~ 'api_configuration_'` returns no rows. Your function is fine, you don't have a table with such name. — Slava Lenskyy, Dec 10 '18 at 16:06
If I run only the select statement, I get all the table names according to the WHERE condition. — Keselme, Dec 10 '18 at 16:09
Your issue is not the function itself but something else (I suspect it's the user access rights). Are you executing the function with same user and database that created it? If not you can also try to create the function with SECURITY DEFINER. [create function doc](https://www.postgresql.org/docs/11/sql-createfunction.html) — Slava Lenskyy, Dec 10 '18 at 16:17
I think you want use [CURSOR](https://www.postgresql.org/docs/current/plpgsql-cursors.html) — Juan Carlos Oropeza, Dec 10 '18 at 16:22
When you said doesnt work what that mean? You get an error or wrong result? — Juan Carlos Oropeza, Dec 10 '18 at 16:23
@JuanCarlosOropeza, I don't get any result at all. No Errors. — Keselme, Dec 10 '18 at 16:24
@JuanCarlosOropeza: The OP already uses a cursor: the implicit cursor of a `FOR` loop, which is typically the best choice - while a set-based solution is not an option, which is typically superior to all looping and cursors. — Erwin Brandstetter, Dec 12 '18 at 00:38

Erwin Brandstetter · Accepted Answer · 2018-12-12T00:26:34.423

You ruled out problems with visibility (missing privileges for tables of interest). See:

How to check if a table exists in a given schema

RAISE NOTICE returns messages - on a different channel than results from a query ("data output"). I suspect one of the following:

Looking in the wrong place? That depends on your undisclosed client. In the standard GUI pgAdmin III or pgAdmin4 there are separate tabs for "Data Output" and "Messages" ...
Is client_min_messages set higher than NOTICE? Try RAISE WARNING in the function; not EXCEPTION though, that cancels execution right away. Or reset the GUC - you can test locally in your current session without touching the server configuration:
```
SET client_min_messages = 'NOTICE';
```
Then try again.

To quickly verify that PL/pgSQL loop and query are working as expected, try this equivalent function with actual data output:

CREATE OR REPLACE FUNCTION test()
  RETURNS SETOF text AS
$func$
DECLARE
   elem text;
BEGIN
   FOR elem IN
      SELECT table_name FROM information_schema.tables
      WHERE  table_name ~ 'api_configuration_'
   LOOP
      RETURN NEXT elem;
      -- RAISE NOTICE 'Table name is: %', elem;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT * FROM test();

How to iterate over SELECT query results in PL/pgSQL?

1 Answers1