Re: [SQL] Related tables to a view

[Jan Meyland Andersen]

 Try this query


 select a.relname as base,a.relkind
 from
 pg_class a
 join pg_depend d on (a.oid = d.refobjid)
 join pg_class c on (d.classid = c.oid)
 join pg_rewrite r on (objid = r.oid)
 join pg_class v on (ev_class = v.oid)
 where a.relkind in('r', 'v')
 and a.relname  v.relname
 and v.relname='YOUR VIEW NAME HERE'
 order by 1


Thanks a lot. That is exactly what I need.

Regards

Jan


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[SQL] Unable to drop table, error mentions reltriggers

[Kashmira Patel \(kupatel\)]

Hello,
   I have two tables as follows:
 
create table vm_message
(
MessageIdvarchar(128)  not null,
MessageType  integer   not null default 1,
UseCount integer   not null default 1,
Sender   varchar(256)  not null,
Urgent   boolean   not null default
false,
Private  boolean   not null default
false,
MessageLengthinteger   ,
MessageSize  integer   ,
MessageTime  bigint,
AttachedMsgIdvarchar(128)  ,
MessageOid   OID   ,
UTCTime  varchar(32)   ,
EndTime  bigintnot null default
9223372036854775807,
ReceiveTime  bigintnot null default 0,
NdrReasoninteger   ,
StartTimebigintdefault 0,
MWIOnboolean   not null default
false,
TotalMessageLength   integer   not null default 0,
uid  serial not null UNIQUE,
CallerPhoneNumbervarchar(15)   not null default '',
foreign key (AttachedMsgId) references vm_message on delete cascade
on update cascade,
primary key (MessageId)
);

create table vm_future_msg_job
(
MessageIdvarchar(128)  not null,
Recipients   varchar(1024) not null,
StartTimebigintnot null,
JobType  int   default 0,
foreign key (MessageId) references vm_message on delete cascade on
update cascade,
primary key (MessageId,Recipients)
);

 
When I try to drop either one of these tables, I get the following
error: 
ERROR: relation vm_message has reltriggers = 0
and the table does not get dropped. 
 
What does this error mean???
 
 

Re: [SQL] Unable to drop table, error mentions reltriggers

[Shoaib Mir]

Run the following:

update pg_class set reltriggers = count(*) from pg_trigger where
pg_class.oid=tgrelid and relname='vm_message';

It should solve the problem

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 12/28/06, Kashmira Patel (kupatel) [EMAIL PROTECTED] wrote:


 Hello,
   I have two tables as follows:

create table vm_message
(
MessageIdvarchar(128)  not null,
MessageType  integer   not null default 1,
UseCount integer   not null default 1,
Sender   varchar(256)  not null,
Urgent   boolean   not null default false,
Private  boolean   not null default false,
MessageLengthinteger   ,
MessageSize  integer   ,
MessageTime  bigint,
AttachedMsgIdvarchar(128)  ,
MessageOid   OID   ,
UTCTime  varchar(32)   ,
EndTime  bigintnot null default
9223372036854775807,
ReceiveTime  bigintnot null default 0,
NdrReasoninteger   ,
StartTimebigintdefault 0,
MWIOnboolean   not null default false,
TotalMessageLength   integer   not null default 0,
uid  serial not null UNIQUE,
CallerPhoneNumbervarchar(15)   not null default '',
foreign key (AttachedMsgId) references vm_message on delete cascade on
update cascade,
primary key (MessageId)
);
create table vm_future_msg_job
(
MessageIdvarchar(128)  not null,
Recipients   varchar(1024) not null,
StartTimebigintnot null,
JobType  int   default 0,
foreign key (MessageId) references vm_message on delete cascade on
update cascade,
primary key (MessageId,Recipients)
);

When I try to drop either one of these tables, I get the following error:
ERROR: relation vm_message has reltriggers = 0
and the table does not get dropped.

What does this error mean???