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