creating a dumpfile from a view
Hi, I'm trying to create a dumpfile for a client. The data is
gathered from about 7 tables, and I need to output all the columns as
the client wishes.
I figure the best way to this is to collect data from multiple tables
and putting them into a view, and using the client's desired names to
be the column headings of this view. Then, I'd dump the data from a
view, and it'll all be ready for delivery.
The problem I run into is that when I dump from a view, I just get
the query that defined it in the first place.
pg_dump --table=demtest dbname
Output:
--
-- PostgreSQL database dump
--
SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = amt, pg_catalog;
--
-- Name: demtest; Type: VIEW; Schema: amt; Owner: postgres
--
CREATE VIEW demtest AS
SELECT foo.serial_no, foo.course_id, foo.writer_id,
foo.assignment_type, bar.critique_serial_no, ...;
ALTER TABLE amt.demtest OWNER TO postgres;
--
-- PostgreSQL database dump complete
--
Would anyone know how to dump the data from the view? I tried the
following, but it doesn't work:
Thanks!
--Richard
Hi!
Richard Yen wrote:
Would anyone know how to dump the data from the view?
You should check COPY command in the manual:
http://www.postgresql.org/docs/current/static/sql-copy.html to save all your
data.
But you have to use tables with copy, not views.
Probably the solution will be to store all needed data in temporary table
and then use COPY to output data to file.
Try something like this:
CREATE VIEW demtest AS
SELECT foo.serial_no, foo.course_id, foo.writer_id,
foo.assignment_type, bar.critique_serial_no, ...;
CREATE TEMPORARY TABLE temp_demtest AS SELECT * FROM demtest;
COPY temp_demtest TO 'output_file_path';
Taras Kopets
On Mon, 2006-10-30 at 16:02 -0800, Richard Yen wrote:
Hi, I'm trying to create a dumpfile for a client. The data is
gathered from about 7 tables, and I need to output all the columns as
the client wishes.
[snip]
Would anyone know how to dump the data from the view? I tried the
following, but it doesn't work:
I often export data with psqland feed to a spreadsheet, like this:
$ psql -F'<tab>' -Ac 'select col1,col2 from aview' >aview.csv
$ OOo aview.csv
Perhaps that will work for you. <tab> is generated by typing ctrl-v,
then <tab>. ctrl-v inserts the next char literally. I dunno about the
ill begotten csh-derivatives. Fortunately, I've never had conflicts with
the delimiter choice and the data. You may not be so lucky.
If you're truly looking for the view data in pg_dump format, the only
thing I can think of is to materialize the view and dump that.
A similar effect could be obtained by synthesizing a create table
statement appropriate for the psql data export as above and using the
copy command to load data. That could all be wrapped into a single file
to be passed to psql for loading.
-Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
On Tue, Oct 31, 2006 at 02:38:27AM +0200, Taras Kopets wrote:
Richard Yen wrote:
Would anyone know how to dump the data from the view?
You should check COPY command in the manual:
http://www.postgresql.org/docs/current/static/sql-copy.html to save all your
data.
But you have to use tables with copy, not views.
That'll change in 8.2. Here's an item from the Release Notes:
* COPY TO can copy the output of an arbitrary SELECT statement
--
Michael Fuhr