Thread: Object description at Client Window

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

----- Original Message -----

From: Jordan S. Jones

To: Kumar

Cc: pgsql-sql@postgresql.org

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 uses.

Jordan S. Jones

Kumar wrote:

-- 
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

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

I am sorry. Yes it worked.
Thank you very much Mr. Jordan and Mr. Richard.

----- 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

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         |
1name               | character varying(100)      | f          | f         |
2website            | character varying(50)       | f          | f         |
3address1           | character varying(100)      | f          | f         |
4address2           | character varying(100)      | f          | f         |
5city               | character varying(50)       | f          | f         |
6state              | character varying(50)       | t          | f         |
7postal_code        | character varying(30)       | t          | f         |
8country            | character varying(50)       | t          | f         |
9account_manager_id | bigint                      | t          | f         |
10primary_contact_id | bigint                      | t          | f         |
11company_type_id    | bigint                      | t          | f         |
12status_flag        | bigint                      | f          | f         |
13lead_source        | bigint                      | f          | f         |
14lead_date          | timestamp without time zone | f          | f         |
15industry_type      | bigint                      | f          | f         |
16rec_modifier_id    | bigint                      | t          | f         |
17rec_created_date   | timestamp without time zone | t          | f         |  18rec_modified_date  | timestamp without
timezone | f          | f         |
 
19rec_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 FORSELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasdef, a.attnumFROM pg_class c,
pg_attributeaWHERE c.relname = p_tablenameAND a.attnum > 0AND a.attrelid = c.oidORDER 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

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" <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
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 

-- 
-Achilleus

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