'Printing to the screen in a .sql file in PostgreSQL
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.
Solution 1:[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 variableECHO
toall
.
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'
Solution 2:[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";
Solution 3:[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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | Peter Mortensen |
Solution 2 | shanemgrey |
Solution 3 | Peter Mortensen |