pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Subject: view on system tables upgrade?
Date: Fri, 26 Apr 2002 17:05:27 METDST
Message-ID: <200204261505.RAA18337@rodos> (raw)

I've moved from 7.1.2 to 7.2.1 and now 
a view does no longer work as intended 

create view pgsql_usertables as 
select 
upper(u.usename) AS TBL_OWNER, upper(t.typname) AS TBL_NAME, 
upper(a.attname) AS COL_NAME, a.atttypid AS COL_TYPE, 
int4larger(a.attlen, a.atttypmod - 4) AS COL_LENGTH, 
CASE WHEN a.attnotnull=true THEN 0 ELSE 1 END AS COL_NULL, a.attnum AS COL_SEQ, 
 CASE WHEN EXISTS(SELECT adsrc FROM pg_attrdef d 
 WHERE d.adrelid = a.attrelid and d.adnum = a.attnum) THEN 
 1
 ELSE
 0 
 END AS COL_DEFAULT 
from pg_user u, pg_type t, pg_attribute a, pg_type n 
where u.usesysid = t.typowner 
and t.typrelid = a.attrelid and t.typtype = 'c' and not (t.typname ~* 'pg_') 
and n.typelem = a.atttypid 
and substr(n.typname, 1, 1) = '_' 
and a.attnum > 0 ;

CREATE TABLE catsource
	(sid		INTEGER		NOT NULL,
	 name		VARCHAR(64) 	NOT NULL,
	 entrancetime	DATETIME	NOT NULL
) ; 

select col_type,col_length,col_null,col_seq,col_name,col_default 
 from pgsql_usertables where tbl_name='CATSOURCE' order by col_seq ; 

retrieves 
 col_type | col_length | col_null | col_seq | col_name | col_default
----------+------------+----------+---------+----------+-------------
       23 |          4 |        0 |       1 | SID      |           0
     1043 |         64 |        0 |       2 | NAME     |           0
(2 rows)

All columns of type timestamp have disappeared. 
Can somebody please give me a hint what happened. 

Regards, Christoph 



view thread (2+ messages)  latest in thread

Message-ID: <200204261505.RAA18337@rodos>
Permalink:  ../200204261505.RAA18337@rodos/
Also on:    postgresql.org/message-id/200204261505.RAA18337@rodos

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: ch@rodos.fzk.de
  Subject: Re: view on system tables upgrade?
  In-Reply-To: <200204261505.RAA18337@rodos>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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