Postgres: how to print values in the loop

Question

I want to print all tables that I got from the query. However my code displays only the name of the function.

# create or replace function remove_scene()
# RETURNS void AS $$
# DECLARE
# row record;
# BEGIN
# FOR row IN
# select table_name from information_schema.tables WHERE table_schema = 'public' AND table_name ~ 'measurement[0-9]'
# LOOP
# RAISE NOTICE '** %', quote_ident(row.table_name);
# END LOOP;
# END;
# $$ LANGUAGE plpgsql;
CREATE FUNCTION
 # select remove_scene();
 remove_scene
--------------

(1 row)

#

user330315 · Accepted Answer · 2014-10-28 14:34:03Z

The reason you are not seeing anything is that your function doesn't return anything. Text that is "written" using RAISE NOTICE will only be displayed in the client if a) the client supports that (which psql does) and b) if your session is configured to actually return them. In order for your raise to be seen in psql you need to use:

set client_min_messages = NOTICE;

Your current setting is probably WARNING and therefor a NOTICE is not displayed.

However: doing this in a loop with RAISE NOTICE is very inefficient. It would be much better to change your function into a set returning function:

create or replace function remove_scene()
  RETURNS table(table_name text) 
AS $$
   select table_name 
   from information_schema.tables 
   WHERE table_schema = 'public' 
     AND table_name ~ 'measurement[0-9]'
$$ 
LANGUAGE sql;

and then use it like this:

select *
from remove_scene();

Another option would be to put that select statement into a view.

Collectives™ on Stack Overflow

Postgres: how to print values in the loop

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related