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

Search Discussions

  • Bosco Rama at Feb 2, 2011 at 8:58 pm

    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.
  • Wim Bertels at Feb 4, 2011 at 9:29 am

    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.
  • Bosco Rama at Feb 4, 2011 at 11:23 am

    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.
  • Wim Bertels at Feb 4, 2011 at 11:28 am

    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.
  • Bosco Rama at Feb 4, 2011 at 5:46 pm

    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedFeb 2, '11 at 3:10p
activeFeb 4, '11 at 5:46p
posts6
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Wim Bertels: 3 posts Bosco Rama: 3 posts

People

Translate

site design / logo © 2021 Grokbase