Printing to screen in .sql file postgres

问题:

This sounds like it should be a very easy thing to do, however, I cannot find ANYWHERE how to do it.

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 Postgres?

回答1:

If you're just feeding a big pile of SQL to psql then you have a couple 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'

回答2:

Via: https://stackoverflow.com/a/18828523/2014857

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.

回答3:

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";