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" <[EMAIL PROTECTED]>
To: "Kumar" <[EMAIL PROTECTED]>; "Jordan S. Jones" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
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
