Printing to the screen in a .sql file in PostgreSQL

Question

I have a .sql file I am building for an upgrade to my application that alters tables, inserts/updates, etc.

I want to write to the screen after every command finishes.

So, for instance if I have something like:

insert into X...

I want to see something like,

Starting to insert into table X

Finished inserting into table X

Is this possible in PostgreSQL?

_{This sounds like it should be a very easy thing to do, however, I cannot find anywhere how to do it.}

Answer 1

If you're just feeding a big pile of SQL to psql then you have a couple of options.

You could run psql with --echo-all:

-a
--echo-all
Print all input lines to standard output as they are read. This is more useful for script processing than interactive mode. This is equivalent to setting the variable ECHO to all.

That and the other "echo everything of this type" options (see the manual) are probably too noisy though. If you just want to print things manually, use \echo:

\echo text [ ... ]
Prints the arguments to the standard output, separated by one space and followed by a newline. This can be useful to intersperse information in the output of scripts.

So you can say:

\echo 'Starting to insert into table X'
-- big pile of inserts go here...
\echo 'Finished inserting into table X'

Answer 2

There's probably a better way to do it. But if you need to use vanilla SQL, try this:

SELECT NULL AS "Starting to insert into table X";
-- big pile of inserts go here...
SELECT NULL AS "Finished inserting into table X";

Answer 3

Via an answer to How can I run an ad-hoc script in PostgreSQL?:

DO language plpgsql $$
BEGIN
  RAISE NOTICE 'Hello, World!';
END
$$;

Depending on what you're doing, I'd be worried about doing a bunch of anonymous code blocks. You might consider storing the above as a function, and passing in whatever value you want logged.