Type: \copyright for distribution=20 terms
       \h for help with SQL=20 commands
       \? for help on internal sl= ash=20 commands
       \g or terminate with semic= olon=20 to execute query
       \q to quit<= /DIV>

training=3D#

But I wanted to know whether this description could be = availed=20 at the command prompt. I am using PgAdmin3. I have checked the systems tabl= es=20 also. pg_tables can tell us only the table and the columns inside=20 tables.

Any idea to share with me, please. I am looking for som= ething=20 like sp_helptext in MS SQL server.

Regards

Kumar

------=_NextPart_000_0051_01C394A2.E6FDA060-- From list@racistnames.com Fri Oct 17 06:21:23 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id EF929D1B544 for ; Fri, 17 Oct 2003 06:21:23 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 72646-06 for ; Fri, 17 Oct 2003 03:20:54 -0300 (ADT) Received: from racistnames.com (unknown [64.5.47.130]) by svr1.postgresql.org (Postfix) with ESMTP id D0960D1B51B for ; Fri, 17 Oct 2003 03:20:52 -0300 (ADT) Received: from racistnames.com (12-254-249-119.client.attbi.com [12.254.249.119]) (authenticated) by racistnames.com (8.11.6/8.11.6) with ESMTP id h9H6KqG08793; Fri, 17 Oct 2003 02:20:52 -0400 Message-ID: <3F8F8A37.1010208@racistnames.com> Date: Fri, 17 Oct 2003 00:20:39 -0600 From: "Jordan S. Jones" User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5) Gecko/20030925 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Kumar Cc: pgsql-sql@postgresql.org Subject: Re: Object description at Client Window References: <005401c39474$db9cf640$7502a8c0@hdsc.com> In-Reply-To: <005401c39474$db9cf640$7502a8c0@hdsc.com> Content-Type: multipart/alternative; boundary="------------080906010204070509000209" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/203 X-Sequence-Number: 15603 This is a multi-part message in MIME format. --------------080906010204070509000209 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit give psql -E a try.. It will display any internal SQL commands that it uses. Jordan S. Jones Kumar wrote: > Dear Friends, > > I am working with Postgres 7.3.4 on RH linux 7.2. > > I could get into the command prompt to describe a table structure. > > Welcome to psql, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help on internal slash commands > \g or terminate with semicolon to execute query > \q to quit > > training=# \d emp > Table "emp" > Column | Type | Modifiers > --------+-----------------------+----------- > no | integer | > name | character varying(20) | > age | integer | > > training=# > > But I wanted to know whether this description could be availed at the > command prompt. I am using PgAdmin3. I have checked the systems tables > also. pg_tables can tell us only the table and the columns inside tables. > > Any idea to share with me, please. I am looking for something like > sp_helptext in MS SQL server. > > Regards > Kumar -- I am nothing but a poor boy. Please Donate.. https://www.paypal.com/xclick/business=list%40racistnames.com&no_note=1&tax=0¤cy_code=USD --------------080906010204070509000209 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit give psql -E a try.. It will display any internal SQL commands that it uses.

Jordan S. Jones

Kumar wrote:

Dear Friends,

I am working with Postgres 7.3.4 on RH linux 7.2.

I could get into the command prompt to describe a table structure.

Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

training=# \d emp
                Table "emp"
Column |         Type          | Modifiers
--------+-----------------------+-----------
no     | integer               |
name   | character varying(20) |
age    | integer               |

training=#

But I wanted to know whether this description could be availed at the command prompt. I am using PgAdmin3. I have checked the systems tables also. pg_tables can tell us only the table and the columns inside tables.

Any idea to share with me, please. I am looking for something like sp_helptext in MS SQL server.

Regards

Kumar

-- 
I am nothing but a poor boy. Please Donate..
https://www.paypal.com/xclick/business=list%40racistnames.com&no_note=1&tax=0&currency_code=USD

--------------080906010204070509000209-- From sgnerd@yahoo.com.sg Fri Oct 17 08:39:53 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id D1C9FD1B532 for ; Fri, 17 Oct 2003 08:39:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 90841-08 for ; Fri, 17 Oct 2003 05:39:24 -0300 (ADT) Received: from smtp016.mail.yahoo.com (smtp016.mail.yahoo.com [216.136.174.113]) by svr1.postgresql.org (Postfix) with SMTP id B6641D1B51B for ; Fri, 17 Oct 2003 05:39:21 -0300 (ADT) Received: from ptil-138-190-chn.primus-india.net (HELO IVESIA67) (sgnerd@203.196.190.138 with login) by smtp.mail.vip.sc5.yahoo.com with SMTP; 17 Oct 2003 08:39:19 -0000 Message-ID: <007b01c3948a$de7800b0$7502a8c0@hdsc.com> Reply-To: "Kumar" From: "Kumar" To: "Jordan S. Jones" Cc: References: <005401c39474$db9cf640$7502a8c0@hdsc.com> <3F8F8A37.1010208@racistnames.com> Subject: Re: Object description at Client Window Date: Fri, 17 Oct 2003 14:14:17 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0070_01C394B8.F3A25020" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/204 X-Sequence-Number: 15604 This is a multi-part message in MIME format. ------=_NextPart_000_0070_01C394B8.F3A25020 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi , Jordan, thanks for ur reply. But I am not asking that. I want to get all the column names of any table at the PgAdmin3 SQL Window.= To make it more clear, actually i wanted to send the table name as the inp= ut parameter for a function and expecting the column names, data types, etc= as the output. Is there any command or any system table from that I could query the column= names of a table (other than \d table name at the command prompt). Kumar ----- Original Message -----=20 From: Jordan S. Jones=20 To: Kumar=20 Cc: pgsql-sql@postgresql.org=20 Sent: Friday, October 17, 2003 11:50 AM Subject: Re: [SQL] Object description at Client Window give psql -E a try.. It will display any internal SQL commands that it us= es. Jordan S. Jones Kumar wrote: Dear Friends, I am working with Postgres 7.3.4 on RH linux 7.2. I could get into the command prompt to describe a table structure. Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit training=3D# \d emp Table "emp" Column | Type | Modifiers --------+-----------------------+----------- no | integer | name | character varying(20) | age | integer | training=3D# But I wanted to know whether this description could be availed at the c= ommand prompt. I am using PgAdmin3. I have checked the systems tables also.= pg_tables can tell us only the table and the columns inside tables. Any idea to share with me, please. I am looking for something like sp_h= elptext in MS SQL server. Regards Kumar --=20 I am nothing but a poor boy. Please Donate.. https://www.paypal.com/xclick/business=3Dlist%40racistnames.com&no_note=3D1= &tax=3D0¤cy_code=3DUSD ------=_NextPart_000_0070_01C394B8.F3A25020 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi ,

Jordan, thanks for ur reply. But I am not asking=20 that.

I want to get all the column names of any table at= the=20 PgAdmin3 SQL Window. To make it more clear, actually i wanted to send the t= able=20 name as the input parameter for a function and expecting the column names, = data=20 types, etc as the output.

Is there any command or any system table from that I co= uld=20 query the column names of a table (other than \d table name at the command= =20 prompt).

Kumar

----- Original Message -----
Fro= m:=20 Jord= an S.=20 Jones

Cc: pgsql-sql@postgresql.org

Sent: Friday, October 17, 2003 11:= 50=20 AM

Subject: Re: [SQL] Object descript= ion at=20 Client Window

give psql -E a try.. It will display any internal SQL comm= ands=20 that it uses.

Jordan S. Jones

Kumar wrote:

Dear Friends,

I am working with Postgres 7.3.4 on RH linux=20 7.2.

I could get into the command prompt to describe a t= able=20 structure.

Welcome to psql, the PostgreSQL interactive=20 terminal.

Type: \copyright for distribution=20 terms
       \h for help with SQL=20 commands
       \? for help on interna= l=20 slash commands
       \g or terminate = with=20 semicolon to execute query
       \q t= o=20 quit

training=3D# \d=20 emp
          &nbs= p;    =20 Table "emp"
Column=20 |        =20 Type          |=20 Modifiers
--------+-----------------------+-----------
no&n= bsp;   =20 |=20 integer          &nbs= p;   =20 |
name   | character varying(20)=20 |
age    |=20 integer          &nbs= p;   =20 |

training=3D#

But I wanted to know whether this description could= be=20 availed at the command prompt. I am using PgAdmin3. I have checked the= =20 systems tables also. pg_tables can tell us only the table and the colum= ns=20 inside tables.

Any idea to share with me, please. I am looking for= =20 something like sp_helptext in MS SQL server.

Regards

Kumar

--=20
I am nothing but a poor boy. Please Donate..
https://www.paypal.com/xclick/business=3Dlist%40racistnames.com&=
;no_note=3D1&tax=3D0&currency_code=3DUSD

------=_NextPart_000_0070_01C394B8.F3A25020-- From dev@archonet.com Fri Oct 17 09:25:24 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6A97FD1B4E5 for ; Fri, 17 Oct 2003 09:25:24 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 00490-10 for ; Fri, 17 Oct 2003 06:24:54 -0300 (ADT) Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net [194.217.242.92]) by svr1.postgresql.org (Postfix) with ESMTP id 7050AD1B4EF for ; Fri, 17 Oct 2003 06:24:47 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-34.mail.demon.net with esmtp (Exim 3.35 #1) id 1AAQqj-0004Re-0Y; Fri, 17 Oct 2003 10:24:46 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id B4876176D8; Fri, 17 Oct 2003 10:24:44 +0100 (BST) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 1D30B17670; Fri, 17 Oct 2003 10:24:44 +0100 (BST) From: Richard Huxton To: "Kumar" , "Jordan S. Jones" Subject: Re: Object description at Client Window Date: Fri, 17 Oct 2003 10:24:17 +0100 User-Agent: KMail/1.5 Cc: References: <005401c39474$db9cf640$7502a8c0@hdsc.com> <3F8F8A37.1010208@racistnames.com> <007b01c3948a$de7800b0$7502a8c0@hdsc.com> In-Reply-To: <007b01c3948a$de7800b0$7502a8c0@hdsc.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200310171024.17494.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/205 X-Sequence-Number: 15605 On Friday 17 October 2003 09:44, Kumar wrote: > Hi , > > Jordan, thanks for ur reply. But I am not asking that. > > I want to get all the column names of any table at the PgAdmin3 SQL Window. > To make it more clear, actually i wanted to send the table name as the > input parameter for a function and expecting the column names, data types, > etc as the output. > > Is there any command or any system table from that I could query the column > names of a table (other than \d table name at the command prompt). Try what the man said. Start psql with -E and issue \d mytable and it will show you the SQL it uses to produce the table's details. -- Richard Huxton Archonet Ltd From sgnerd@yahoo.com.sg Fri Oct 17 12:05:32 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A31B9D1B545 for ; Fri, 17 Oct 2003 12:05:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 17442-09 for ; Fri, 17 Oct 2003 09:05:04 -0300 (ADT) Received: from smtp016.mail.yahoo.com (smtp016.mail.yahoo.com [216.136.174.113]) by svr1.postgresql.org (Postfix) with SMTP id 4D992D1B53B for ; Fri, 17 Oct 2003 09:05:00 -0300 (ADT) Received: from ptil-138-190-chn.primus-india.net (HELO IVESIA67) (sgnerd@203.196.190.138 with login) by smtp.mail.vip.sc5.yahoo.com with SMTP; 17 Oct 2003 12:05:02 -0000 Message-ID: <00d401c394a7$9bc6eb60$7502a8c0@hdsc.com> Reply-To: "Kumar" From: "Kumar" To: "Richard Huxton" , "Jordan S. Jones" Cc: References: <005401c39474$db9cf640$7502a8c0@hdsc.com> <3F8F8A37.1010208@racistnames.com> <007b01c3948a$de7800b0$7502a8c0@hdsc.com> <200310171024.17494.dev@archonet.com> Subject: Re: Object description at Client Window Date: Fri, 17 Oct 2003 17:39:52 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/207 X-Sequence-Number: 15607 I am sorry. Yes it worked. Thank you very much Mr. Jordan and Mr. Richard. ----- Original Message ----- From: "Richard Huxton" To: "Kumar" ; "Jordan S. Jones" Cc: Sent: Friday, October 17, 2003 2:54 PM Subject: Re: [SQL] Object description at Client Window > On Friday 17 October 2003 09:44, Kumar wrote: > > Hi , > > > > Jordan, thanks for ur reply. But I am not asking that. > > > > I want to get all the column names of any table at the PgAdmin3 SQL Window. > > To make it more clear, actually i wanted to send the table name as the > > input parameter for a function and expecting the column names, data types, > > etc as the output. > > > > Is there any command or any system table from that I could query the column > > names of a table (other than \d table name at the command prompt). > > Try what the man said. Start psql with -E and issue \d mytable and it will > show you the SQL it uses to produce the table's details. > > -- > Richard Huxton > Archonet Ltd From sgnerd@yahoo.com.sg Fri Oct 17 12:33:16 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5F487D1B532 for ; Fri, 17 Oct 2003 12:33:16 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 33750-04 for ; Fri, 17 Oct 2003 09:32:47 -0300 (ADT) Received: from smtp017.mail.yahoo.com (smtp017.mail.yahoo.com [216.136.174.114]) by svr1.postgresql.org (Postfix) with SMTP id 4FACAD1B4EF for ; Fri, 17 Oct 2003 09:32:44 -0300 (ADT) Received: from ptil-138-190-chn.primus-india.net (HELO IVESIA67) (sgnerd@203.196.190.138 with login) by smtp.mail.vip.sc5.yahoo.com with SMTP; 17 Oct 2003 12:32:45 -0000 Message-ID: <00ea01c394ab$7b0576e0$7502a8c0@hdsc.com> Reply-To: "Kumar" From: "Kumar" To: "Richard Huxton" , "Jordan S. Jones" Cc: References: <005401c39474$db9cf640$7502a8c0@hdsc.com> <3F8F8A37.1010208@racistnames.com> <007b01c3948a$de7800b0$7502a8c0@hdsc.com> <200310171024.17494.dev@archonet.com> Subject: Re: Object description at Client Window Date: Fri, 17 Oct 2003 18:07:38 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/208 X-Sequence-Number: 15608 But I have get into another problem. While I execute the following command I could get the result as U can see below etgsuite=# SELECT a.attname,format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasd ef, a.attnum FROM pg_class c, pg_attribute a WHERE c.relname = 'companies' AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum; attname | format_type | attnotnull | atthasdef | attnum --------------------+-----------------------------+------------+-----------+ -------- company_id | bigint | t | t | 1 name | character varying(100) | f | f | 2 website | character varying(50) | f | f | 3 address1 | character varying(100) | f | f | 4 address2 | character varying(100) | f | f | 5 city | character varying(50) | f | f | 6 state | character varying(50) | t | f | 7 postal_code | character varying(30) | t | f | 8 country | character varying(50) | t | f | 9 account_manager_id | bigint | t | f | 10 primary_contact_id | bigint | t | f | 11 company_type_id | bigint | t | f | 12 status_flag | bigint | f | f | 13 lead_source | bigint | f | f | 14 lead_date | timestamp without time zone | f | f | 15 industry_type | bigint | f | f | 16 rec_modifier_id | bigint | t | f | 17 rec_created_date | timestamp without time zone | t | f | 18 rec_modified_date | timestamp without time zone | f | f | 19 rec_deleted_flag | character(1) | t | f | 20 (20 rows) So I tried to create a plpgsql function as follows to return these for all the table name. So I have created a function like this CREATE OR REPLACE FUNCTION public.desc_table(varchar) RETURNS refcursor AS 'DECLARE ref REFCURSOR ; p_tablename ALIAS FOR $1; BEGIN OPEN ref FOR SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasdef, a.attnum FROM pg_class c, pg_attribute a WHERE c.relname = p_tablename AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum; RETURN ref; END;' LANGUAGE 'plpgsql' VOLATILE; While trying to execute this select desc_table('companies'); I got the following error. WARNING: Error occurred while executing PL/pgSQL function desc_table WARNING: line 7 at open ERROR: Unable to identify an operator '=' for types 'name' and 'character varying' You will have to retype this query using an explicit cast I have write many functions of the same structure and executed with out problems. Where I am doing wrong here. Please shed some light. Regards Kumar ----- Original Message ----- From: "Richard Huxton" To: "Kumar" ; "Jordan S. Jones" Cc: Sent: Friday, October 17, 2003 2:54 PM Subject: Re: [SQL] Object description at Client Window > On Friday 17 October 2003 09:44, Kumar wrote: > > Hi , > > > > Jordan, thanks for ur reply. But I am not asking that. > > > > I want to get all the column names of any table at the PgAdmin3 SQL Window. > > To make it more clear, actually i wanted to send the table name as the > > input parameter for a function and expecting the column names, data types, > > etc as the output. > > > > Is there any command or any system table from that I could query the column > > names of a table (other than \d table name at the command prompt). > > Try what the man said. Start psql with -E and issue \d mytable and it will > show you the SQL it uses to produce the table's details. > > -- > Richard Huxton > Archonet Ltd From achill@matrix.gatewaynet.com Fri Oct 17 14:20:12 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5EE2AD1B4E3 for ; Fri, 17 Oct 2003 14:20:12 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 54108-06 for ; Fri, 17 Oct 2003 11:19:45 -0300 (ADT) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by svr1.postgresql.org (Postfix) with ESMTP id CCC83D1B532 for ; Fri, 17 Oct 2003 11:19:38 -0300 (ADT) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id h9HEXFdN005703; Fri, 17 Oct 2003 17:33:15 +0300 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id h9HEXF0j005699; Fri, 17 Oct 2003 17:33:15 +0300 Date: Fri, 17 Oct 2003 17:33:15 +0300 (EEST) From: achill@matrix.gatewaynet.com To: Kumar Cc: pgsql-sql@postgresql.org Subject: Re: Object description at Client Window In-Reply-To: <00ea01c394ab$7b0576e0$7502a8c0@hdsc.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/210 X-Sequence-Number: 15610 On Fri, 17 Oct 2003, Kumar wrote: > But I have get into another problem. While I execute the following command I > could get the result as U can see below > > etgsuite=# SELECT a.attname,format_type(a.atttypid, a.atttypmod), > a.attnotnull, a.atthasd > ef, a.attnum > FROM pg_class c, pg_attribute a > WHERE c.relname = 'companies' > AND a.attnum > 0 AND a.attrelid = c.oid > ORDER BY a.attnum; > attname | format_type | attnotnull | atthasdef | > attnum > --------------------+-----------------------------+------------+-----------+ > -------- > company_id | bigint | t | t | > 1 > name | character varying(100) | f | f | > 2 > website | character varying(50) | f | f | > 3 > address1 | character varying(100) | f | f | > 4 > address2 | character varying(100) | f | f | > 5 > city | character varying(50) | f | f | > 6 > state | character varying(50) | t | f | > 7 > postal_code | character varying(30) | t | f | > 8 > country | character varying(50) | t | f | > 9 > account_manager_id | bigint | t | f | > 10 > primary_contact_id | bigint | t | f | > 11 > company_type_id | bigint | t | f | > 12 > status_flag | bigint | f | f | > 13 > lead_source | bigint | f | f | > 14 > lead_date | timestamp without time zone | f | f | > 15 > industry_type | bigint | f | f | > 16 > rec_modifier_id | bigint | t | f | > 17 > rec_created_date | timestamp without time zone | t | f | > 18 > rec_modified_date | timestamp without time zone | f | f | > 19 > rec_deleted_flag | character(1) | t | f | > 20 > (20 rows) > > So I tried to create a plpgsql function as follows to return these for all > the table name. So I have created a function like this > > CREATE OR REPLACE FUNCTION public.desc_table(varchar) > RETURNS refcursor AS > 'DECLARE > > ref REFCURSOR ; > p_tablename ALIAS FOR $1; > > BEGIN > OPEN ref FOR > SELECT a.attname, > format_type(a.atttypid, a.atttypmod), > a.attnotnull, > a.atthasdef, > a.attnum > FROM pg_class c, pg_attribute a > WHERE c.relname = p_tablename > AND a.attnum > 0 > AND a.attrelid = c.oid > ORDER BY a.attnum; > > RETURN ref; > END;' > LANGUAGE 'plpgsql' VOLATILE; > > > While trying to execute this > select desc_table('companies'); > > I got the following error. > WARNING: Error occurred while executing PL/pgSQL function desc_table > WARNING: line 7 at open > > ERROR: Unable to identify an operator '=' for types 'name' and 'character > varying' > You will have to retype this query using an explicit cast replace WHERE c.relname = p_tablename with WHERE c.relname::varchar = p_tablename > > I have write many functions of the same structure and executed with out > problems. Where I am doing wrong here. > > Please shed some light. > > Regards > Kumar > > ----- Original Message ----- > From: "Richard Huxton" > To: "Kumar" ; "Jordan S. Jones" > Cc: > Sent: Friday, October 17, 2003 2:54 PM > Subject: Re: [SQL] Object description at Client Window > > > > On Friday 17 October 2003 09:44, Kumar wrote: > > > Hi , > > > > > > Jordan, thanks for ur reply. But I am not asking that. > > > > > > I want to get all the column names of any table at the PgAdmin3 SQL > Window. > > > To make it more clear, actually i wanted to send the table name as the > > > input parameter for a function and expecting the column names, data > types, > > > etc as the output. > > > > > > Is there any command or any system table from that I could query the > column > > > names of a table (other than \d table name at the command prompt). > > > > Try what the man said. Start psql with -E and issue \d mytable and it will > > show you the SQL it uses to produce the table's details. > > > > -- > > Richard Huxton > > Archonet Ltd > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- -Achilleus From georgew1@mts.net Fri Oct 17 14:50:07 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id CC792D1B53D for ; Fri, 17 Oct 2003 14:50:07 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 55728-09 for ; Fri, 17 Oct 2003 11:49:40 -0300 (ADT) Received: from smtp2.mts.net (smtp2.mts.net [205.200.16.113]) by svr1.postgresql.org (Postfix) with ESMTP id 7477FD1B4E3 for ; Fri, 17 Oct 2003 11:49:35 -0300 (ADT) Received: from cleartag (wnpgmb01bbz-ac04-p13-42.mts.net [142.161.13.42]) by smtp2.mts.net (8.12.8-20030918/8.11.3) with SMTP id h9HEnbNs023981; Fri, 17 Oct 2003 09:49:38 -0500 (CDT) Message-ID: <005f01c394bd$ea11f100$2a0da18e@cleartag> From: "George Weaver" To: "Kumar" Cc: References: <005401c39474$db9cf640$7502a8c0@hdsc.com> <3F8F8A37.1010208@racistnames.com> <007b01c3948a$de7800b0$7502a8c0@hdsc.com> <200310171024.17494.dev@archonet.com> <00ea01c394ab$7b0576e0$7502a8c0@hdsc.com> Subject: Re: Object description at Client Window Date: Fri, 17 Oct 2003 09:49:13 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/211 X-Sequence-Number: 15611 Kumar, pg_class.relname is type "name". You are trying to compare it to p_tablename which is type "varchar". Try changing your function definition to: CREATE OR REPLACE FUNCTION public.desc_table(name) ^^^^ HTH George SNIP > CREATE OR REPLACE FUNCTION public.desc_table(varchar) > RETURNS refcursor AS > 'DECLARE > > ref REFCURSOR ; > p_tablename ALIAS FOR $1; > > BEGIN > OPEN ref FOR > SELECT a.attname, > format_type(a.atttypid, a.atttypmod), > a.attnotnull, > a.atthasdef, > a.attnum > FROM pg_class c, pg_attribute a > WHERE c.relname = p_tablename > AND a.attnum > 0 > AND a.attrelid = c.oid > ORDER BY a.attnum; > > RETURN ref; > END;' > LANGUAGE 'plpgsql' VOLATILE; > > > While trying to execute this > select desc_table('companies'); > > I got the following error. > WARNING: Error occurred while executing PL/pgSQL function desc_table > WARNING: line 7 at open > > ERROR: Unable to identify an operator '=' for types 'name' and 'character > varying' > You will have to retype this query using an explicit cast > SNIP