How to print to console in SQL?

Question

I'm using PostgreSQL And I want to print a message to the console.

If I use plpythonu I use plpy.notice If I use plpgsql I use raise notice

but how do I print when the function is pure SQL? using SELECT 'string' isn't helping me as it print the string in a column and if the message is located in the middle of the code it doesn't show it. I want something like raise notice / plpy.notice for SQL.

CREATE OR REPLACE FUNCTION A()
  RETURNS VOID AS
$BODY$ 
                how do i print 'hello world' here?
 $BODY$
  LANGUAGE sql VOLATILE

if it was a plpgsql I would do:

CREATE OR REPLACE FUNCTION A()
  RETURNS VOID AS
$BODY$ 
        Raise Notice 'hello world'
 $BODY$
  LANGUAGE plpgsql VOLATILE

I'm looking for the equivalent in LANGUAGE SQL

http://stackoverflow.com/questions/17693259/need-to-printing-postgresql-database-console check this out. — S. M. Shahinul Islam, Jul 07 '15 at 05:57
doesn't help me. the function there is in plpgsql. for plpgsql you can do Raise Notice. I want the equivalent command for SQL function. — aayushdagra, Jul 07 '15 at 06:00
Cross post: http://stackoverflow.com/questions/31260497/how-to-print-to-console-in-sql — , Jul 07 '15 at 06:10
Maybe a SELECT 'hello world'? Other than that, I used a solution for something else here: http://dba.stackexchange.com/a/24850/6219 — András Váczi, Jul 07 '15 at 08:26

score 19 · Answer 1 · answered Jan 23 '18 at 16:18

With psql you could \echo text (or \qecho if using \o), for example executing with psql -f a file with contents:

select 'Straight';
\echo BETWEEN
select 'the lines.';

outputs:

 ?column? 
----------
 Straight
(1 row)

BETWEEN
  ?column?  
------------
 the lines.
(1 row)

score 9 · Answer 2 · answered Jul 07 '15 at 09:08

Why not just call the plpgsql function that raises a notice, from inside the SQL function?

According to the doc:

SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list

So a SELECT statement can be inserted at the beginning without changing the result.

create function print(text) returns void as 
$$ begin raise notice '%', $1; end 
$$ language plpgsql; 

create function whatever(int) returns int as $$
  select print('foobar');
  select $1;
 $$ language sql;

select whatever(3);
NOTICE:  foobar
CONTEXT:  SQL function "whatever" statement 1
 whatever 
----------
        3
(1 row)

score 9 · Answer 3 · answered Dec 01 '16 at 20:41

9

You can execute bash commands from psql. For example:

\! echo "HERE YOUR DEBUG MSG!"
select * from your_table;

answered Dec 01 '16 at 20:41

0x1337

191
1
2

While this is true, you cannot do this from inside a SQL function. – András Váczi Dec 01 '16 at 21:04

How to print to console in SQL?

3 Answers3