Re: pg_get_tabledef

From: "Usama Munir" <usama(dot)munir(at)enterprisedb(dot)com>
To: "Naz Gassiep" <naz(at)mira(dot)net>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_get_tabledef
Date: 2007-05-21 15:31:41
Message-ID: 4651BB5D.8070806@enterprisedb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
I think using pg_dump in some cases is a good option , but not all the
time, having a function makes it much cleaner to use<br>
<br>
Consider pgAdmin lets say (and there are many such applications out
there) , you need to show object DDL on the RHP and its nicely
formatted and you can copy paste it and then perhaps export it in an
SQL file.<br>
<br>
Now imagine you need to spawn a new process from inside the app for
pg_dump, and then make it write to a file and then read the file to
display the object DDL, which is possible but very messy looking code.
Then there are issues with launching external processes on certain
platforms (for example in Java if you start a new process from the
runtime(), you need to make sure you properly flush out its stdout and
stderr streams otherwise it can go in a deadlock etc), i would use a
function, if available anyday <br>
<br>
Additionally&nbsp; there are such functions for other objects, but for
tables you needed to construct it manually, so i also thought this
would just complete the set and make it easier to write an SQL / DDL
exporting app.<br>
<br>
<br>
<br>
Naz Gassiep wrote:
<blockquote cite="mid4651B51F(dot)3000908(at)mira(dot)net" type="cite">
<pre wrap="">Just a question, is there any advantage to having this then building a
function in applications that wrap and use pg_dump with a few options?
Surely that's a more appropriate way to achieve this functionality?
- Naz.

Usama Munir wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi,

i was following a thread some time ago where adding a function
*pg_get_tabledef* was one of the TODOs for 8.2, but it somehow didn't
make it to the release perhaps because the functionality was not
clearly defined? not sure.

Anyway i happen to come up with a function for pg_get_tabledef which
works something like following

/postgres=# select pg_get_tabledef(16388) ;
pg_get_tabledef
--------------------------------
CREATE TABLE public.dept
(
deptno numeric(2,0) NOT NULL,
dname character varying(14),
loc character varying(13)
)
WITHOUT OIDS;
/(1 row)

i wanted to submit a patch for this, IFF the community wants this
function. The rationale is obviously to help Application developers
writing applications like pgAdmin. Currently this part of SQL needs to
be constructed manually for postgres by the tools.

it is arguable that a table defintion will have constraints , triggers
etc as well, and they can be added without much problem, but i think
if a tool needs to construct an SQL for all table related objects then
functions are already available for them like pg_get_constraintdef,
pg_get_ruledef, pg_get_indexdef, pg_get_triggerdef etc

i understand that you guys don't like someone to develop a patch in
isolation and just come up with it one day, but it really came out as
a by-product of some other work , and i thought you guys might be
interested in it.

if it is desired, i will submit a patch for it, within a day or so.

Regards,
Usama Munir
EnterpriseDB (<a class="moz-txt-link-abbreviated" href="http://www.enterprisedb.com">www.enterprisedb.com</a>)

</pre>
</blockquote>
<pre wrap=""><!---->
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
</pre>
</blockquote>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 3.5 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2007-05-21 16:20:55 Re: pg_get_tabledef
Previous Message Tom Lane 2007-05-21 15:26:25 Re: pg_get_tabledef