pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Help converting constraint triggers
5+ messages / 3 participants
[nested] [flat]

* Help converting constraint triggers
@ 2005-08-03 15:27 Chris Hoover <revoohc@gmail.com>
  2005-08-03 15:53 ` Re: Help converting constraint triggers Tom Lane <tgl@sss.pgh.pa.us>
  2005-08-03 16:24 ` Re: Help converting constraint triggers Chris Hoover <revoohc@gmail.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Chris Hoover @ 2005-08-03 15:27 UTC (permalink / raw)
  To: pgsql-admin

I need some help understanding how to convert constraint triggers to
normal foreign keys.  I have several of them in my database that a
previous dba created.  Since they don't show up in most PostgreSQL
tools, they are becoming a real pain to deal with.

Here is an example of on of the constraint triggers:
CREATE CONSTRAINT TRIGGER sys_user_group_id_fkey
    AFTER INSERT OR UPDATE ON sys_user
    FROM sys_group
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_check_ins" ('sys_user_group_id_fkey',
'sys_user', 'sys_group', 'UNSPECIFIED', 'group_id', 'group_id');


I believe this is the same as the foriegn key definition,

alter table sys_user 
  add constraint sys_user_group_id_fkey foreign key(group_id)
  references sys_group(group_id)
  not deferrrable;

Is this correct?  If not, how would be proper foreign key be defined?

Thanks,

Chris



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Help converting constraint triggers
  2005-08-03 15:27 Help converting constraint triggers Chris Hoover <revoohc@gmail.com>
@ 2005-08-03 15:53 ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-08-03 16:26   ` Re: Help converting constraint triggers Chris Hoover <revoohc@gmail.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Tom Lane @ 2005-08-03 15:53 UTC (permalink / raw)
  To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin

Chris Hoover <revoohc@gmail.com> writes:
> I need some help understanding how to convert constraint triggers to
> normal foreign keys.

You might try the contrib/adddepend script ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Help converting constraint triggers
  2005-08-03 15:27 Help converting constraint triggers Chris Hoover <revoohc@gmail.com>
  2005-08-03 15:53 ` Re: Help converting constraint triggers Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-08-03 16:26   ` Chris Hoover <revoohc@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Chris Hoover @ 2005-08-03 16:26 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

I tried running this an a test db, and it got some, but not all of the
constraint triggers.  Is there anything else to try?

Also, was my interpretation of the constraint trigger correct?  While
there are many constraint triggers defined, I don't think the volume
is large enough to make it prohibitive for me to just convert them by
hand.

Thanks,

Chris

On 8/3/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Chris Hoover <revoohc@gmail.com> writes:
> > I need some help understanding how to convert constraint triggers to
> > normal foreign keys.
> 
> You might try the contrib/adddepend script ...
> 
>                         regards, tom lane
>



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Help converting constraint triggers
  2005-08-03 15:27 Help converting constraint triggers Chris Hoover <revoohc@gmail.com>
@ 2005-08-03 16:24 ` Chris Hoover <revoohc@gmail.com>
  2005-08-03 17:23   ` Re: Help converting constraint triggers Hemapriya <priyam_1121@yahoo.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Chris Hoover @ 2005-08-03 16:24 UTC (permalink / raw)
  To: Hemapriya <priyam_1121@yahoo.com>; pgsql-admin

What version of pgadmin are you using?  I looked in pgadmin3 and it
does not show the constraint triggers.

On 8/3/05, Hemapriya <priyam_1121@yahoo.com> wrote:
> 
> pgadmin will list all the trigger functions defined
> in each schema. it is a freeware.
> 
> --- Chris Hoover <revoohc@gmail.com> wrote:
> 
> > I need some help understanding how to convert
> > constraint triggers to
> > normal foreign keys.  I have several of them in my
> > database that a
> > previous dba created.  Since they don't show up in
> > most PostgreSQL
> > tools, they are becoming a real pain to deal with.
> >
> > Here is an example of on of the constraint triggers:
> > CREATE CONSTRAINT TRIGGER sys_user_group_id_fkey
> >     AFTER INSERT OR UPDATE ON sys_user
> >     FROM sys_group
> >     NOT DEFERRABLE INITIALLY IMMEDIATE
> >     FOR EACH ROW
> >     EXECUTE PROCEDURE "RI_FKey_check_ins"
> > ('sys_user_group_id_fkey',
> > 'sys_user', 'sys_group', 'UNSPECIFIED', 'group_id',
> > 'group_id');
> >
> >
> > I believe this is the same as the foriegn key
> > definition,
> >
> > alter table sys_user
> >   add constraint sys_user_group_id_fkey foreign
> > key(group_id)
> >   references sys_group(group_id)
> >   not deferrrable;
> >
> > Is this correct?  If not, how would be proper
> > foreign key be defined?
> >
> > Thanks,
> >
> > Chris
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map
> > settings
> >
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Help converting constraint triggers
  2005-08-03 15:27 Help converting constraint triggers Chris Hoover <revoohc@gmail.com>
  2005-08-03 16:24 ` Re: Help converting constraint triggers Chris Hoover <revoohc@gmail.com>
@ 2005-08-03 17:23   ` Hemapriya <priyam_1121@yahoo.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Hemapriya @ 2005-08-03 17:23 UTC (permalink / raw)
  To: Chris Hoover <revoohc@gmail.com>; pgsql-admin

pgAdmin III v1.2.0..

--- Chris Hoover <revoohc@gmail.com> wrote:

> What version of pgadmin are you using?  I looked in
> pgadmin3 and it
> does not show the constraint triggers.
> 
> On 8/3/05, Hemapriya <priyam_1121@yahoo.com> wrote:
> > 
> > pgadmin will list all the trigger functions
> defined
> > in each schema. it is a freeware.
> > 
> > --- Chris Hoover <revoohc@gmail.com> wrote:
> > 
> > > I need some help understanding how to convert
> > > constraint triggers to
> > > normal foreign keys.  I have several of them in
> my
> > > database that a
> > > previous dba created.  Since they don't show up
> in
> > > most PostgreSQL
> > > tools, they are becoming a real pain to deal
> with.
> > >
> > > Here is an example of on of the constraint
> triggers:
> > > CREATE CONSTRAINT TRIGGER sys_user_group_id_fkey
> > >     AFTER INSERT OR UPDATE ON sys_user
> > >     FROM sys_group
> > >     NOT DEFERRABLE INITIALLY IMMEDIATE
> > >     FOR EACH ROW
> > >     EXECUTE PROCEDURE "RI_FKey_check_ins"
> > > ('sys_user_group_id_fkey',
> > > 'sys_user', 'sys_group', 'UNSPECIFIED',
> 'group_id',
> > > 'group_id');
> > >
> > >
> > > I believe this is the same as the foriegn key
> > > definition,
> > >
> > > alter table sys_user
> > >   add constraint sys_user_group_id_fkey foreign
> > > key(group_id)
> > >   references sys_group(group_id)
> > >   not deferrrable;
> > >
> > > Is this correct?  If not, how would be proper
> > > foreign key be defined?
> > >
> > > Thanks,
> > >
> > > Chris
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 5: don't forget to increase your free space
> map
> > > settings
> > >
> > 
> > 
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam
> protection around
> > http://mail.yahoo.com
> >
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2005-08-03 17:23 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-03 15:27 Help converting constraint triggers Chris Hoover <revoohc@gmail.com>
2005-08-03 15:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-03 16:26   ` Chris Hoover <revoohc@gmail.com>
2005-08-03 16:24 ` Chris Hoover <revoohc@gmail.com>
2005-08-03 17:23   ` Hemapriya <priyam_1121@yahoo.com>

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