Message-ID: <00ea01c394ab$7b0576e0$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Richard Huxton" <dev@archonet.com>,
	"Jordan S. Jones" <list@racistnames.com>
Cc: <pgsql-sql@postgresql.org>
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

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" <dev@archonet.com>
To: "Kumar" <sgnerd@yahoo.com.sg>; "Jordan S. Jones" <list@racistnames.com>
Cc: <pgsql-sql@postgresql.org>
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