Thread: redirecting query statement and output to a marked up file, using psql
Hallo, goal: to write the input and query results to different files in 1 script context example: 1 sql script demo.sql " --init messaging \set client_min_messages warning \set log_error_verbosity terse --user1 SET SESSION AUTHORIZATION user1; \pset format html \o report/test_user1.html \i structure/test_user1.sql --user2 SET SESSION AUTHORIZATION user2; \pset format latex \echo ECHO queries \o report/test_user2.tex \i structure/test_user2.sql " This doenst seem to work, as the ECHO queries output isnt written to the file (test_user2.tex) The only work around i found so far, is using redirection. starting for example > psql -L out.txt # only txt markup or > psql > out.txt But then if have to postprocess the out.txt file, dividing it into several different files, and the format/markup doenst work so nice any more. Suggestions? tnx, Wim
Wim Bertels wrote: > > --user2 > SET SESSION AUTHORIZATION user2; > \pset format latex > \echo ECHO queries > \o report/test_user2.tex > \i structure/test_user2.sql > " > > This doenst seem to work, > as the ECHO queries output isnt written to the file (test_user2.tex) Actions are performed as they are encountered so put the \echo *after* the \o, like this: SET SESSION AUTHORIZATION user2; \pset format latex \o report/test_user2.tex \echo ECHO queries \i structure/test_user2.sql HTH, Bosco.
On Wed, 2011-02-02 at 12:49 -0800, Bosco Rama wrote: > Wim Bertels wrote: > > > > --user2 > > SET SESSION AUTHORIZATION user2; > > \pset format latex > > \echo ECHO queries > > \o report/test_user2.tex > > \i structure/test_user2.sql > > " > > > > This doenst seem to work, > > as the ECHO queries output isnt written to the file (test_user2.tex) > > Actions are performed as they are encountered so put the \echo *after* the > \o, like this: > > SET SESSION AUTHORIZATION user2; > \pset format latex > \o report/test_user2.tex > \echo ECHO queries > \i structure/test_user2.sql Hallo Bosco, i tried changing that, but it doesnt seem to work (the echo only affects the psql cmdl, but is not written to /o file) i am using a buil script, eg psql -f init/test_build_psql.sql dev or in psql> \i init/test_build_psql.sql with the contents of test_build_psql.sql being: " \set client_min_messages warning \set log_error_verbosity terse SET SESSION AUTHORIZATION user1; \i init/test_create.sql \i init/test_insert.sql SET SESSION AUTHORIZATION userX; \i init/test_grant.sql \i functions/Trigger_functions.sql SET SEARCH_PATH TO s1, s2, s3, s4; --functions report \pset format html \o report/functions_report.html \df --test student \o report/test_student.html \set ECHO queries \i init/test_student_try_out.sql --undo some settings \pset format aligned \set ECHO \o \set client_min_messages notice \set log_error_verbosity verbose " mvg, Wim > > HTH, > Bosco. >
Wim Bertels wrote: > On Wed, 2011-02-02 at 12:49 -0800, Bosco Rama wrote: >> Wim Bertels wrote: >> > >> > --user2 >> > SET SESSION AUTHORIZATION user2; >> > \pset format latex >> > \echo ECHO queries >> > \o report/test_user2.tex >> > \i structure/test_user2.sql >> > " >> > >> > This doenst seem to work, >> > as the ECHO queries output isnt written to the file (test_user2.tex) >> >> Actions are performed as they are encountered so put the \echo *after* the >> \o, like this: >> >> SET SESSION AUTHORIZATION user2; >> \pset format latex >> \o report/test_user2.tex >> \echo ECHO queries >> \i structure/test_user2.sql > > Hallo Bosco, > > i tried changing that, but it doesnt seem to work > (the echo only affects the psql cmdl, but is not written to /o file) Apologies, Wim. I meant to also indicate that you need to use the \qecho command. It echoes to the query output stream whereas \echo echoes to the stdout. So you would use: \qecho ECHO queries instead of the \echo above. Hopefully I didn't forget anything else this time. :-) HTH Bosco.
On Fri, 2011-02-04 at 03:23 -0800, Bosco Rama wrote: > Wim Bertels wrote: > > On Wed, 2011-02-02 at 12:49 -0800, Bosco Rama wrote: > >> Wim Bertels wrote: > >> > > >> > --user2 > >> > SET SESSION AUTHORIZATION user2; > >> > \pset format latex > >> > \echo ECHO queries > >> > \o report/test_user2.tex > >> > \i structure/test_user2.sql > >> > " > >> > > >> > This doenst seem to work, > >> > as the ECHO queries output isnt written to the file (test_user2.tex) > >> > >> Actions are performed as they are encountered so put the \echo *after* the > >> \o, like this: > >> > >> SET SESSION AUTHORIZATION user2; > >> \pset format latex > >> \o report/test_user2.tex > >> \echo ECHO queries > >> \i structure/test_user2.sql > > > > Hallo Bosco, > > > > i tried changing that, but it doesnt seem to work > > (the echo only affects the psql cmdl, but is not written to /o file) > > Apologies, Wim. I meant to also indicate that you need to use the \qecho > command. It echoes to the query output stream whereas \echo echoes to the > stdout. So you would use: > \qecho ECHO queries Hi Bosco, \qecho doenst interpret parameters it just echo text, in this case 'ECHO queries' mvg, Wim > > instead of the \echo above. > > Hopefully I didn't forget anything else this time. :-) > > HTH > > Bosco. >
Wim Bertels wrote: > > \qecho doenst interpret parameters it just echo text, in this case 'ECHO > queries' Seems like you had two problems and I didn't see any reference to the second one initially. The first was the output of \echo going to the wrong place which is fixed by using \qecho. The second problem is that you are looking to have the variable 'ECHO' replaced in the \qecho command with its current value. This is done using the variable substitution syntax (i.e. the variable name within a pair of colons) like this: \set ECHO Hello \o testfile.txt \qecho :ECHO: world \q Will cause the testfile.txt file to have a line that reads: Hello world HTH Bosco.