Res: [SQL] Finding all tables that have foreign keys referencing a table

2008-11-07 Thread paulo matadr 
Try this

select table_schema, table_name
from information_schema.columns
where table_schema not in
('information_schema','pg_catalog')
and column_name = '?'



De: Andreas Joseph Krogh <[EMAIL PROTECTED]>
Para: pgsql-sql@postgresql.org
Enviadas: Quinta-feira, 6 de Novembro de 2008 19:35:23
Assunto: [SQL] Finding all tables that have foreign keys referencing a table

Hi.
I see the last comment here suggests a solution for $subject:
http://www.postgresql.org/docs/8.1/interactive/ddl-constraints.html

But it turns out it assumes constraint_name is unique, which is not the case. 
It is only unique pr. table. In other words, it produces a lot of false results.

I'm trying to make a function which finds all my old "$1" constraints and 
replaces those names with proper names (_fkey).

So - anybody who knows a good solution for how to find all tables with 
constraint-names that have foreign keys referencing a table's particluar colum?

-- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / CEO
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



  Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua 
cara @ymail.com ou @rocketmail.com.
http://br.new.mail.yahoo.com/addresses

[SQL] sum timestamp result in hours

2008-11-07 Thread paulo matadr 
Select sum(age(rgat.rgat_tmencerramento, rgat.rgat_tmregistroatendimento))
from atendimentopublico.registro_atendimento rgat
inner join cadastro.localidade loca on loca.loca_id=rgat.loca_id
inner join atendimentopublico.solicitacao_tipo_especificacao step on 
step.step_id=rgat.step_id
where
date(rgat_tmregistroatendimento) between '2007-01-01' and '2007-12-31'
and rgat.step_id in 
(706,710,717,718,719,722,725,728,730,732,733,720,721,727,714,729)
and rgat.rgat_cdsituacao=2
and date(rgat_tmencerramento) between '2007-01-01' and '2007-12-31'
and rgat.rgat_tmencerramento > rgat.rgat_tmregistroatendimento
and rgat.loca_id=339
and rgat.rgat_id in
(20111305,
30102409,
30102096,
30097214,
30102396,
20057815,
20325895)
Result: 1 year 4 mons 88 days 51:42:00

I need help to view result query only in hours, the timestamp columns in  bold 
above.
thanks
Paulo Moraes


  Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua 
cara @ymail.com ou @rocketmail.com.
http://br.new.mail.yahoo.com/addresses

Res: [SQL] sum timestamp result in hours

2008-11-07 Thread paulo matadr 
I change form to create query  but basically need
sum(rgat.rgat_tmencerramento - rgat.rgat_tmregistroatendimento) = result in 
hours

sumary:
rgat.rgat_tmencerrament = timestamp column
gat.rgat_tmregistroatendimento = timestamp column








De: Osvaldo Kussama <[EMAIL PROTECTED]>
Para: paulo matadr <[EMAIL PROTECTED]>
Enviadas: Sexta-feira, 7 de Novembro de 2008 12:50:14
Assunto: Re: [SQL] sum timestamp result in hours

2008/11/7, paulo matadr <[EMAIL PROTECTED]>:
> Select sum(age(rgat.rgat_tmencerramento, rgat.rgat_tmregistroatendimento))
> from atendimentopublico.registro_atendimento rgat
> inner join cadastro.localidade loca on loca.loca_id=rgat.loca_id
> inner join atendimentopublico.solicitacao_tipo_especificacao step on
> step.step_id=rgat.step_id
> where
> date(rgat_tmregistroatendimento) between '2007-01-01' and '2007-12-31'
> and rgat.step_id in
> (706,710,717,718,719,722,725,728,730,732,733,720,721,727,714,729)
> and rgat.rgat_cdsituacao=2
> and date(rgat_tmencerramento) between '2007-01-01' and '2007-12-31'
> and rgat.rgat_tmencerramento > rgat.rgat_tmregistroatendimento
> and rgat.loca_id=339
> and rgat.rgat_id in
> (20111305,
> 30102409,
> 30102096,
> 30097214,
> 30102396,
> 20057815,
> 20325895)
> Result: 1 year 4 mons 88 days 51:42:00
>
> I need help to view result query only in hours, the timestamp columns in
> bold above.
>


88 days? 51 hours?

Try:
bdteste=# SELECT EXTRACT(EPOCH FROM INTERVAL '1 year 4 mons 88 days
51:42:00')/3600;
?column?
--
  13809.7
(1 registro)

Osvaldo



  Novos endereços, o Yahoo! que você conhece. Crie um email novo com a sua 
cara @ymail.com ou @rocketmail.com.
http://br.new.mail.yahoo.com/addresses

[SQL] Enc: Help to replace caracter

2008-11-14 Thread paulo matadr 





I Need to replace string (" ) in the situation below :
 
select  clie_nmclientefrom cadastro.cliente where clie_nmcliente like 
'%"%'; 
 
result:
JOANA D"ARCALMEIDA"
EMLURB "P M R."
CECILIA D"CAGNO"
HELENA FERREIRA D"FREITAS"
JOSE M. "BARRACA DO BOLA"
FORTE" DUNAS BAR"
JOANA D"ARC R. DE SOUZA
ASSEMBLEIA DE DEUS"
USINA SALGADO"SUPRIMENTO 
JOSE MOURA  'BIGODE"
BEATRIZ MEDEIROS D"EMERY

Any help me to create pgPL/sql or funcion  to replace ( " )  to null value, I 
have many table with this.
Thanks for help



  Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com

Res: [SQL] UPDATE and DELEte with a lot of register is to slow...

2008-11-20 Thread paulo matadr 
did you make  analyse(vacuum ) in you database?






De: Craig Ringer <[EMAIL PROTECTED]>
Para: John Dizaro <[EMAIL PROTECTED]>
Cc: pgsql-sql@postgresql.org
Enviadas: Quinta-feira, 20 de Novembro de 2008 8:29:55
Assunto: Re: [SQL] UPDATE and DELEte with a lot of register is to slow...

John Dizaro wrote:
> I have   to execute some times an UPDATE  an some times a DELETE  to
> register +- 5  , every time when i do that  the database  be  to slow
> do we any way to DELETE or UPDATE by part???

I'm not sure your post really contains enough information to answer your 
question.

If you are having problems with slow DELETEs, could it be a problem with a 
foreign key that lacks an index on the referencing (rather than referenced) 
column?

What do you mean by "to register 5" ? Do you mean "affecting 50,000 rows" ?

Just how slow do you mean? Is the problem that the UPDATE or DELETE takes a 
long time, or that it slows down other queries?

--
Craig Ringer

-- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



  Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com

[SQL] Relatorio da composiçao de FKs e PKs

2009-03-19 Thread paulo matadr 
Bom tarde mais um vez , minha intensao era fazer um relatorio da composicao de 
cada fk e  pk( nao apenas seu nome),alguem sabe onde o postgres guarda isso no 
catalogo.
Abraço a todos.


  Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com