Re: Object description at Client Window

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