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

Answer 1

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)

Answer 2

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)

Answer 3

You can execute bash commands from psql. For example:

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