Thread: saving a query and reults to a file.
Is there an easy way to save a series of queries and query results to a single file like /tee in MySQL? I am running version 8.-beta4 on windows XP.
On Tue, 2005-11-29 at 11:35 -0500, T. Lackey wrote: > Is there an easy way to save a series of queries and query results to > a single file like /tee in MySQL? I am running version 8.-beta4 on > windows XP. In psql: \o output_file_name ...various queries... \o -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html
am 29.11.2005, um 18:38:50 +0000 mailte Oliver Elphick folgendes: > On Tue, 2005-11-29 at 11:35 -0500, T. Lackey wrote: > > Is there an easy way to save a series of queries and query results to > > a single file like /tee in MySQL? I am running version 8.-beta4 on > > windows XP. > > In psql: > > \o output_file_name > ...various queries... > \o This isn't the right answer. \o redirect _only_ query results to the file, not the queries. And yes, i don't know the answer. Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
On Tue, 2005-11-29 at 20:47 +0100, A. Kretschmer wrote: > am 29.11.2005, um 18:38:50 +0000 mailte Oliver Elphick folgendes: > > On Tue, 2005-11-29 at 11:35 -0500, T. Lackey wrote: > > > Is there an easy way to save a series of queries and query results to > > > a single file like /tee in MySQL? I am running version 8.-beta4 on > > > windows XP. > > > > In psql: > > > > \o output_file_name > > ...various queries... > > \o > > This isn't the right answer. \o redirect _only_ query results to the > file, not the queries. > > And yes, i don't know the answer. Of course, you are right. I tried psql -e and -a and -L and none of them do it. The only way to get the queries into the output file as well is to copy them there separately: \o output_file_name \qecho SELECT * FROM my_table; SELECT * FROM my_table; \o It's a bit cumbersome. The other way is to capture the whole session output. In Linux you can do that with the script command, but I don't know if Windows has any decent tools like that. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html
PGAdmin3 gives the user the option to run a query and output it to a file. It is a "play" style triangle with a floppy disk icon - 4the from the right on the latest version of pgadmin3. not sure if this helps or not. --- Oliver Elphick <olly@lfix.co.uk> wrote: > On Tue, 2005-11-29 at 20:47 +0100, A. Kretschmer > wrote: > > am 29.11.2005, um 18:38:50 +0000 mailte Oliver > Elphick folgendes: > > > On Tue, 2005-11-29 at 11:35 -0500, T. Lackey > wrote: > > > > Is there an easy way to save a series of > queries and query results to > > > > a single file like /tee in MySQL? I am > running version 8.-beta4 on > > > > windows XP. > > > > > > In psql: > > > > > > \o output_file_name > > > ...various queries... > > > \o > > > > This isn't the right answer. \o redirect _only_ > query results to the > > file, not the queries. > > > > And yes, i don't know the answer. > > Of course, you are right. I tried psql -e and -a > and -L and none of > them do it. The only way to get the queries into > the output file as > well is to copy them there separately: > > \o output_file_name > \qecho SELECT * FROM my_table; > SELECT * FROM my_table; > \o > > It's a bit cumbersome. > > The other way is to capture the whole session > output. In Linux you can > do that with the script command, but I don't know if > Windows has any > decent tools like that. > > -- > Oliver Elphick > olly@lfix.co.uk > Isle of Wight > http://www.lfix.co.uk/oliver > GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 > 5664 7A2F A543 10EA > > ======================================== > Do you want to know God? > http://www.lfix.co.uk/knowing_god.html > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
On Tue, 2005-11-29 at 15:21 -0800, operationsengineer1@yahoo.com wrote: > PGAdmin3 gives the user the option to run a query and > output it to a file. It is a "play" style triangle > with a floppy disk icon - 4the from the right on the > latest version of pgadmin3. > > not sure if this helps or not. I'm afradi not. It doesn't output the query -- only the results. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html
I am the person who originally posted the question. I teach an introductory database class so it is very beneficial for students to have both the query and results in the same file. MySQL uses "tee or \T" and appends everything to the designated output file. I do not particularly like the way I am implementing this in psql, but after reviewing suggested solutions it appears there is not much choice. I use the script below by entering the command "\i c:/tee.txt". The file tee.txt contains the following. \o c:/psqlResults1.txt \e \o \w c:/psqlQuery1.txt \! copy c:\\psqlQuery1.txt+c:\\psqlResults1.txt c:\\psqlAll.txt The script puts you into the command buffer. When exiting the command buffer the query is run. The query is saved in psqlQuery1.txt, the results are in the psqlResults1.txt. and everything is in psqlAll.txt. A few more DOS commands can be added to the script to include a file with blank lines in the COPY for spacing, DELete for the files you do not want to keep around, etc. I still would like to see a better way to do this. Thank you for all of your help. ----- Original Message ----- From: <operationsengineer1@yahoo.com> To: <olly@lfix.co.uk>; "A. Kretschmer" <andreas.kretschmer@schollglas.com> Cc: "Christian Hofmann" <christian.hofmann@gmx.de>; <pgsql-novice@postgresql.org> Sent: Tuesday, November 29, 2005 6:21 PM Subject: Re: [NOVICE] saving a query and reults to a file. PGAdmin3 gives the user the option to run a query and output it to a file. It is a "play" style triangle with a floppy disk icon - 4the from the right on the latest version of pgadmin3. not sure if this helps or not. --- Oliver Elphick <olly@lfix.co.uk> wrote: > On Tue, 2005-11-29 at 20:47 +0100, A. Kretschmer > wrote: > > am 29.11.2005, um 18:38:50 +0000 mailte Oliver > Elphick folgendes: > > > On Tue, 2005-11-29 at 11:35 -0500, T. Lackey > wrote: > > > > Is there an easy way to save a series of > queries and query results to > > > > a single file like /tee in MySQL? I am > running version 8.-beta4 on > > > > windows XP. > > > > > > In psql: > > > > > > \o output_file_name > > > ...various queries... > > > \o > > > > This isn't the right answer. \o redirect _only_ > query results to the > > file, not the queries. > > > > And yes, i don't know the answer. > > Of course, you are right. I tried psql -e and -a > and -L and none of > them do it. The only way to get the queries into > the output file as > well is to copy them there separately: > > \o output_file_name > \qecho SELECT * FROM my_table; > SELECT * FROM my_table; > \o > > It's a bit cumbersome. > > The other way is to capture the whole session > output. In Linux you can > do that with the script command, but I don't know if > Windows has any > decent tools like that. > > -- > Oliver Elphick > olly@lfix.co.uk > Isle of Wight > http://www.lfix.co.uk/oliver > GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 > 5664 7A2F A543 10EA > > ======================================== > Do you want to know God? > http://www.lfix.co.uk/knowing_god.html > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
i think anything that makes it easier to teach postgresql to newbies is worth is a good thing. you can post a request over at pgsql-hackers... http://www.postgresql.org/community/lists/ perhaps they can help. you also may want to visit pgadmin3's developers mailing list and see if they can add the feature. best of luck. --- "T. Lackey" <lackey@ltu.edu> wrote: > I am the person who originally posted the question. > I teach an introductory > database class so it is very beneficial for students > to have both the query > and results in the same file. MySQL uses "tee or > \T" and appends everything > to the designated output file. I do not particularly > like the way I am > implementing this in psql, but after reviewing > suggested solutions it > appears there is not much choice. I use the script > below by entering the > command "\i c:/tee.txt". The file tee.txt contains > the following. > > > > \o c:/psqlResults1.txt > > \e > > \o > > \w c:/psqlQuery1.txt > > \! copy c:\\psqlQuery1.txt+c:\\psqlResults1.txt > c:\\psqlAll.txt > > > > > > The script puts you into the command buffer. When > exiting the command buffer > the query is run. The query is saved in > psqlQuery1.txt, the results are in > the psqlResults1.txt. and everything is in > psqlAll.txt. > > > > A few more DOS commands can be added to the script > to include a file with > blank lines in the COPY for spacing, DELete for the > files you do not want to > keep around, etc. > > > > I still would like to see a better way to do this. > Thank you for all of your > help. > > ----- Original Message ----- > From: <operationsengineer1@yahoo.com> > To: <olly@lfix.co.uk>; "A. Kretschmer" > <andreas.kretschmer@schollglas.com> > Cc: "Christian Hofmann" <christian.hofmann@gmx.de>; > <pgsql-novice@postgresql.org> > Sent: Tuesday, November 29, 2005 6:21 PM > Subject: Re: [NOVICE] saving a query and reults to a > file. > > > PGAdmin3 gives the user the option to run a query > and > output it to a file. It is a "play" style triangle > with a floppy disk icon - 4the from the right on the > latest version of pgadmin3. > > not sure if this helps or not. > > --- Oliver Elphick <olly@lfix.co.uk> wrote: > > > On Tue, 2005-11-29 at 20:47 +0100, A. Kretschmer > > wrote: > > > am 29.11.2005, um 18:38:50 +0000 mailte Oliver > > Elphick folgendes: > > > > On Tue, 2005-11-29 at 11:35 -0500, T. Lackey > > wrote: > > > > > Is there an easy way to save a series of > > queries and query results to > > > > > a single file like /tee in MySQL? I am > > running version 8.-beta4 on > > > > > windows XP. > > > > > > > > In psql: > > > > > > > > \o output_file_name > > > > ...various queries... > > > > \o > > > > > > This isn't the right answer. \o redirect _only_ > > query results to the > > > file, not the queries. > > > > > > And yes, i don't know the answer. > > > > Of course, you are right. I tried psql -e and -a > > and -L and none of > > them do it. The only way to get the queries into > > the output file as > > well is to copy them there separately: > > > > \o output_file_name > > \qecho SELECT * FROM my_table; > > SELECT * FROM my_table; > > \o > > > > It's a bit cumbersome. > > > > The other way is to capture the whole session > > output. In Linux you can > > do that with the script command, but I don't know > if > > Windows has any > > decent tools like that. > > > > -- > > Oliver Elphick > > olly@lfix.co.uk > > Isle of Wight > > http://www.lfix.co.uk/oliver > > GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E > 1EC0 > > 5664 7A2F A543 10EA > > > > ======================================== > > Do you want to know God? > > http://www.lfix.co.uk/knowing_god.html > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > > > > __________________________________ > Yahoo! Mail - PC Magazine Editors' Choice 2005 > http://mail.yahoo.com > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to > majordomo@postgresql.org so that your > message can get through to the mailing list > cleanly > __________________________________________ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com