0

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

2 Answers 2

3

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] >file 2>&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=> \set var 'foo'
test=> set params.v1 to :'var';
SET
test=> show params.v1;
 params.v1 
-----------
 foo
(1 row)

test=> do $$ begin raise notice '%', current_setting('params.v1'); end $$;
NOTICE:  foo
DO
2
  • the problem is that even when i use PERFORM statement the output file is still empty , i think that at least the time which has been set when using \timing on needs to be displayed ? Commented Apr 20, 2020 at 17:17
  • Please the edit with the doc quote. Commented Apr 21, 2020 at 9:02
1

You don't need to do shell redirection nor use \o in the SQL script. psql has a flag for this:

-o filename --output=filename

Put all query output into file filename. This is equivalent to the command \o.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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