Обсуждение: Getting source code for database objects

On pgadmin, when you click on a table or function, you get the source
code (DDL) to create that table or function.

I want to take that and check it into subversion so I have one file for
each table, function, view, etc. My question is, how do you get that
source code? I've been playing with pgadmin and wireshark trying to
figure out what commands or queries it is using to no avail.

Artacus

Artacus wrote:
> On pgadmin, when you click on a table or function, you get the source
> code (DDL) to create that table or function.
>
> I want to take that and check it into subversion so I have one file for
> each table, function, view, etc. My question is, how do you get that
> source code? I've been playing with pgadmin and wireshark trying to
> figure out what commands or queries it is using to no avail.

Fire up psql -E and do a few \d commands.  It'll show you the SQL used
to describe the objects.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Artacus wrote:
> On pgadmin, when you click on a table or function, you get the source
> code (DDL) to create that table or function.
>
> I want to take that and check it into subversion so I have one file for
> each table, function, view, etc. My question is, how do you get that
> source code? I've been playing with pgadmin and wireshark trying to
> figure out what commands or queries it is using to no avail.
>
>
See GetSql method of schema/*.cpp

This used to be on the list of possible PG projects for the google
summer of code but seems to have been removed.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

Artacus wrote:
> On pgadmin, when you click on a table or function, you get the source
> code (DDL) to create that table or function.
>
> I want to take that and check it into subversion so I have one file for
> each table, function, view, etc. My question is, how do you get that
> source code? I've been playing with pgadmin and wireshark trying to
> figure out what commands or queries it is using to no avail.

Easier would be just uing pg_dump -s >schema.sql to get all schema objects
so you could check them into subversion. If you want only specific
objects, pg_dump -l >listofobjects, then edit this list as you
like and use pg_dump -L listofobjects >someobjects.sql

This should get you going.

Cheers
Tino

> Easier would be just uing pg_dump -s >schema.sql to get all schema objects
> so you could check them into subversion. If you want only specific
> objects, pg_dump -l >listofobjects, then edit this list as you
> like and use pg_dump -L listofobjects >someobjects.sql

The -l and -L options are not recognized on my server 8.3.

I can use -t to iterate thru each table, but I don't see a way to do one
function at a time.

Artacus wrote:
>
>> Easier would be just uing pg_dump -s >schema.sql to get all schema
>> objects
>> so you could check them into subversion. If you want only specific
>> objects, pg_dump -l >listofobjects, then edit this list as you
>> like and use pg_dump -L listofobjects >someobjects.sql
>
> The -l and -L options are not recognized on my server 8.3.
>
> I can use -t to iterate thru each table, but I don't see a way to do one
> function at a time.

They're part of pg_restore, not pg_dump. You need to use -F c with the
pg_dump to let pg_restore generate lists in this way.

--
   Richard Huxton
   Archonet Ltd

Richard Huxton wrote:
> Artacus wrote:
>>
>>> Easier would be just uing pg_dump -s >schema.sql to get all schema
>>> objects
>>> so you could check them into subversion. If you want only specific
>>> objects, pg_dump -l >listofobjects, then edit this list as you
>>> like and use pg_dump -L listofobjects >someobjects.sql
>>
>> The -l and -L options are not recognized on my server 8.3.
>>
>> I can use -t to iterate thru each table, but I don't see a way to do
>> one function at a time.
>
> They're part of pg_restore, not pg_dump. You need to use -F c with the
> pg_dump to let pg_restore generate lists in this way.
>
Ah yes, correct. Thats how it is when you write this from memory instead
of trying it ;-) I'm always operating on the pg_dump -F c file for
consistency.

T.