pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedObject description at Client Window
8+ messages / 5 participants
[nested] [flat]
* Object description at Client Window
@ 2003-10-17 06:06 Kumar <sgnerd@yahoo.com.sg>
2003-10-17 06:20 ` Re: Object description at Client Window Jordan S. Jones <list@racistnames.com>
0 siblings, 1 reply; 8+ messages in thread
From: Kumar @ 2003-10-17 06:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Object description at Client Window
2003-10-17 06:06 Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
@ 2003-10-17 06:20 ` Jordan S. Jones <list@racistnames.com>
2003-10-17 08:44 ` Re: Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
0 siblings, 1 reply; 8+ messages in thread
From: Jordan S. Jones @ 2003-10-17 06:20 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
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_c...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Object description at Client Window
2003-10-17 06:06 Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
2003-10-17 06:20 ` Re: Object description at Client Window Jordan S. Jones <list@racistnames.com>
@ 2003-10-17 08:44 ` Kumar <sgnerd@yahoo.com.sg>
2003-10-17 09:24 ` Re: Object description at Client Window Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Kumar @ 2003-10-17 08:44 UTC (permalink / raw)
To: Jordan S. Jones <list@racistnames.com>; +Cc: pgsql-sql
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).
Kumar
----- 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:
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_c...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Object description at Client Window
2003-10-17 06:06 Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
2003-10-17 06:20 ` Re: Object description at Client Window Jordan S. Jones <list@racistnames.com>
2003-10-17 08:44 ` Re: Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
@ 2003-10-17 09:24 ` Richard Huxton <dev@archonet.com>
2003-10-17 12:09 ` Re: Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
2003-10-17 12:37 ` Re: Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
0 siblings, 2 replies; 8+ messages in thread
From: Richard Huxton @ 2003-10-17 09:24 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; Jordan S. Jones <list@racistnames.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Object description at Client Window
2003-10-17 06:06 Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
2003-10-17 06:20 ` Re: Object description at Client Window Jordan S. Jones <list@racistnames.com>
2003-10-17 08:44 ` Re: Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
2003-10-17 09:24 ` Re: Object description at Client Window Richard Huxton <dev@archonet.com>
@ 2003-10-17 12:09 ` Kumar <sgnerd@yahoo.com.sg>
1 sibling, 0 replies; 8+ messages in thread
From: Kumar @ 2003-10-17 12:09 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Jordan S. Jones <list@racistnames.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Object description at Client Window
2003-10-17 06:06 Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
2003-10-17 06:20 ` Re: Object description at Client Window Jordan S. Jones <list@racistnames.com>
2003-10-17 08:44 ` Re: Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
2003-10-17 09:24 ` Re: Object description at Client Window Richard Huxton <dev@archonet.com>
@ 2003-10-17 12:37 ` Kumar <sgnerd@yahoo.com.sg>
2003-10-17 14:33 ` Re: Object description at Client Window achill@matrix.gatewaynet.com
2003-10-17 14:49 ` Re: Object description at Client Window George Weaver <georgew1@mts.net>
1 sibling, 2 replies; 8+ messages in thread
From: Kumar @ 2003-10-17 12:37 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Jordan S. Jones <list@racistnames.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Object description at Client Window
2003-10-17 06:06 Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
2003-10-17 06:20 ` Re: Object description at Client Window Jordan S. Jones <list@racistnames.com>
2003-10-17 08:44 ` Re: Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
2003-10-17 09:24 ` Re: Object description at Client Window Richard Huxton <dev@archonet.com>
2003-10-17 12:37 ` Re: Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
@ 2003-10-17 14:33 ` achill@matrix.gatewaynet.com
1 sibling, 0 replies; 8+ messages in thread
From: achill@matrix.gatewaynet.com @ 2003-10-17 14:33 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Object description at Client Window
2003-10-17 06:06 Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
2003-10-17 06:20 ` Re: Object description at Client Window Jordan S. Jones <list@racistnames.com>
2003-10-17 08:44 ` Re: Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
2003-10-17 09:24 ` Re: Object description at Client Window Richard Huxton <dev@archonet.com>
2003-10-17 12:37 ` Re: Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
@ 2003-10-17 14:49 ` George Weaver <georgew1@mts.net>
1 sibling, 0 replies; 8+ messages in thread
From: George Weaver @ 2003-10-17 14:49 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-10-17 14:49 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-17 06:06 Object description at Client Window Kumar <sgnerd@yahoo.com.sg>
2003-10-17 06:20 ` Jordan S. Jones <list@racistnames.com>
2003-10-17 08:44 ` Kumar <sgnerd@yahoo.com.sg>
2003-10-17 09:24 ` Richard Huxton <dev@archonet.com>
2003-10-17 12:09 ` Kumar <sgnerd@yahoo.com.sg>
2003-10-17 12:37 ` Kumar <sgnerd@yahoo.com.sg>
2003-10-17 14:33 ` achill@matrix.gatewaynet.com
2003-10-17 14:49 ` George Weaver <georgew1@mts.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox