Postgres Pro
Downloads
Home   >   mailing lists

Thread: creating a dumpfile from a view

creating a dumpfile from a view

From
Richard Yen
Date:
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

Re: creating a dumpfile from a view

From
"Taras Kopets"
Date:
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

Re: creating a dumpfile from a view

From
Reece Hart
Date:
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

Re: creating a dumpfile from a view

From
Michael Fuhr
Date:
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