From spotluri@ismartpanache.com Wed Apr 9 13:51:03 2008 Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 86B392E003D for ; Wed, 9 Apr 2008 10:51:03 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 04224-03 for ; Wed, 9 Apr 2008 10:50:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from app02.sm.onr.chicago.hostway (fw.mfg.onr.siteprotect.com [64.26.60.152]) by developer.postgresql.org (Postfix) with ESMTP id AEAA12E0038 for ; Wed, 9 Apr 2008 10:50:46 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by app02.sm.onr.chicago.hostway (Postfix) with ESMTP id AAC77C4123 for ; Wed, 9 Apr 2008 08:50:44 -0500 (CDT) MIME-Version: 1.0 X-Mailer: AtMail PHP 5.04 Message-ID: <41734.1207749044@ismartpanache.com> To: Reply-To: spotluri@ismartpanache.com Content-Type: text/html; charset="utf-8" X-Origin: 121.97.28.3 Date: Wed, 9 Apr 2008 08:50:44 -0500 Subject: How do i get DDL of a Table from system catalog ? From: "Potluri Srikanth" Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.673 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.672 X-Spam-Level: * X-Archive-Number: 200804/89 X-Sequence-Number: 28853 Hi,

How do i get DDL of a Table from system catalog ?

Purpose :
1) i need to create an external table on the basis of  another table.<= br>      create table ext_table (like table_name); --------= ----------> this doesnot work for external tables.
2)  so i need to query catalog to get  definition of the table.      on the basis of this definition i can create external t= able .
eg: i am looking for some thing like :

   select * from pg_catalog where table_name=3D' srikanth_ext_par= allel_crt_sms_logs_child_depth_1_d_2008_03_30_daily ' ;

3) once i get the definition of the table , i need to use that definition f= or creation of external table in a script which will run in cron job daily = for data loading.

Can anyone guide/help me in this ?



Regards,
Srikanth k Potluri
+63 9177444783(philippines)
From vka@mgcp.com Wed Apr 9 14:21:39 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 1974A2E002C for ; Wed, 9 Apr 2008 11:21:39 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 97160-02 for ; Wed, 9 Apr 2008 11:21:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.169]) by developer.postgresql.org (Postfix) with ESMTP id E16C62E0050 for ; Wed, 9 Apr 2008 11:21:33 -0300 (ADT) Received: by wf-out-1314.google.com with SMTP id 28so2489055wff.28 for ; Wed, 09 Apr 2008 07:21:32 -0700 (PDT) Received: by 10.143.159.11 with SMTP id l11mr48596wfo.186.1207750892203; Wed, 09 Apr 2008 07:21:32 -0700 (PDT) Received: by 10.143.196.2 with HTTP; Wed, 9 Apr 2008 07:21:32 -0700 (PDT) Message-ID: <9b1af80e0804090721l42e45f09p37ce25ff39e87832@mail.gmail.com> Date: Wed, 9 Apr 2008 18:21:32 +0400 From: "Vyacheslav Kalinin" To: spotluri@ismartpanache.com Subject: Re: How do i get DDL of a Table from system catalog ? Cc: pgsql-admin@postgresql.org In-Reply-To: <41734.1207749044@ismartpanache.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4363_17509020.1207750892229" References: <41734.1207749044@ismartpanache.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/91 X-Sequence-Number: 28855 ------=_Part_4363_17509020.1207750892229 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline try starting psql with -E (would show queries psql does) option and \d+ the table ------=_Part_4363_17509020.1207750892229 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline try starting psql with -E (would show queries psql does) option and \d+ the table



------=_Part_4363_17509020.1207750892229-- From aroravishal22@hotmail.com Thu Apr 10 04:06:34 2008 Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id AD5BB2E0055 for ; Thu, 10 Apr 2008 01:06:34 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 25144-06 for ; Thu, 10 Apr 2008 01:06:27 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from blu139-omc1-s21.blu139.hotmail.com (blu139-omc1-s21.blu139.hotmail.com [65.55.175.161]) by developer.postgresql.org (Postfix) with ESMTP id 0F4542E0054 for ; Thu, 10 Apr 2008 01:06:27 -0300 (ADT) Received: from BLU110-W39 ([65.55.162.182]) by blu139-omc1-s21.blu139.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Wed, 9 Apr 2008 21:06:25 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_42346998-9f4d-47eb-849a-8c318807eec1_" X-Originating-IP: [59.160.193.34] From: Vishal Arora To: , Subject: Re: How do i get DDL of a Table from system catalog ? Date: Thu, 10 Apr 2008 09:36:25 +0530 Importance: Normal In-Reply-To: <41734.1207749044@ismartpanache.com> References: <41734.1207749044@ismartpanache.com> MIME-Version: 1.0 X-OriginalArrivalTime: 10 Apr 2008 04:06:25.0902 (UTC) FILETIME=[3EDB58E0:01C89AC0] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.062 tagged_above=0 required=5 tests=AWL=0.061, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200804/92 X-Sequence-Number: 28856 --_42346998-9f4d-47eb-849a-8c318807eec1_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable To: pgsql-admin@postgresql.orgDate: Wed, 9 Apr 2008 08:50:44 -0500Subject: = [ADMIN] How do i get DDL of a Table from system catalog ?From: spotluri@ism= artpanache.comHi,How do i get DDL of a Table from system catalog ?Purpose := 1) i need to create an external table on the basis of another table. c= reate table ext_table (like table_name); ------------------> this doesnot w= ork for external tables. =20 try - create table ext_table (like pg_catalog.table_name); - it worked for = me. =20 =20 2) so i need to query catalog to get definition of the table. on the = basis of this definition i can create external table .eg: i am looking for = some thing like : select * from pg_catalog where table_name=3D' srikanth_= ext_parallel_crt_sms_logs_child_depth_1_d_2008_03_30_daily ' ;3) once i get= the definition of the table , i need to use that definition for creation o= f external table in a script which will run in cron job daily for data load= ing.Can anyone guide/help me in this ?Regards, Srikanth k Potluri +63 91774= 44783(philippines)=20 _________________________________________________________________ Technology : Catch up on updates on the latest Gadgets, Reviews, Gaming and= Tips to use technology etc. http://computing.in.msn.com/= --_42346998-9f4d-47eb-849a-8c318807eec1_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable





To: pgsql-admin@postgresql.org
Date: Wed, 9 Apr 2008 08:50:44 -0500
S= ubject: [ADMIN] How do i get DDL of a Table from system catalog ?
From: = spotluri@ismartpanache.com

Hi,

How do i get DDL of a Table fr= om system catalog ?

Purpose :
1) i need to create an external tab= le on the basis of  another table.
     create = table ext_table (like table_name); ------------------> this doesnot work= for external tables.
 
try - create table ext_table (like pg_catalog.table_n= ame); - it worked for me.
 
 

2)  so i need to query catalog to get  definition of the tabl= e.
     on the basis of this definition i can create exte= rnal table .
eg: i am looking for some thing like :

   = select * from pg_catalog where table_name=3D' srikanth_ext_parallel_crt_sms= _logs_child_depth_1_d_2008_03_30_daily ' ;

3) once i get the definit= ion of the table , i need to use that definition for creation of external t= able in a script which will run in cron job daily for data loading.

= Can anyone guide/help me in this ?



Regards,
Srikanth k P= otluri
+63 9177444783(philippines)



Exc= lusive Marriage Proposals! Find UR life partner at Shaadi.com Try it! = --_42346998-9f4d-47eb-849a-8c318807eec1_--