output psql results and errors to a file I want to display the results of psql queries on a file.In the log just the name DO for the anonymous Bloc is being displayed.

The following is being executed from a shell script file : 

psql --host=${HOST} --port=${PORT_IP} --dbname=${DATABASE_NAME} --username=${USERNAME} -f $scriptSQL -v v1=$fichierLog -v v2=$annee


and here is the content of scriptSQL :

\o :v1
\timing on
DO $$
declare
    -- programme de lancement de la purge des redevances

    -- date creation : 20/02/09

    annee       varchar(4); --  annee a purger

begin

    annee := ':v2';



  -- lancement de la fonction de purge
    SELECT pckg_activ.purgeRedevance(annee);

end $$;

\timing off
\o
\q


nothing is being displayed apart from DO.

Is the DO Anonymous Bloc the reason why nothing is displayed on v1 (the output file) ?

Any solutions ?

Thanks
RAISE NOTICE must be used to display values in a DO block,
as in

DO $$ begin raise notice 'hello'; end $$;


Result:

NOTICE:  hello


If you do just a SELECT, here's what happens:

DO $$ begin select 'hello'; end $$;
ERROR:  query has no destination for result data
HINT : If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT : PL/pgSQL function inline_code_block line 1 at SQL statement


Since it's an error, it doesn't go into the file designated by \o :v1

The output of \timing also won't go into that file.

The documentation says about \o:


  \o or \out [ filename ] \o or \out [ |command ]
     Arranges to save future query results to the file filename or pipe
     future results to the shell command command. If no argument is
     specified, the query output is reset to the standard output.
  
  ...
  
  “Query results” includes all tables, command responses, and notices
     obtained from the database server, as well as output of various
     backslash commands that query the database (such as \d); but not
     error messages.


To capture all the output, use redirection at the shell:

$ psql [options] &gt;file 2&gt;&amp;1




Another problem is that the :v2 variable cannot be expanded in the dollar-dollar-quoted string that constitutes the DO block (and it should be written as :'v2', not ':v2', but anyway it doesn't matter here because there's no variable expansion in $$-quoted strings )

Both input parameters and output results are sadly difficult to manage with DO blocks.

For the input side, custom session parameters can be used, as shown below:

test=&gt; \set var 'foo'
test=&gt; set params.v1 to :'var';
SET
test=&gt; show params.v1;
 params.v1 
-----------
 foo
(1 row)

test=&gt; do $$ begin raise notice '%', current_setting('params.v1'); end $$;
NOTICE:  foo
DO