7

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

9

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)
|improve this answer|||||
7

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)
|improve this answer|||||
6

You can execute bash commands from psql. For example:

\! echo "HERE YOUR DEBUG MSG!"
select * from your_table;
|improve this answer|||||
  • While this is true, you cannot do this from inside a SQL function. – dezso Dec 1 '16 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.