pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

integrity of column used to order rows
2+ messages / 2 participants
[nested] [flat]

* integrity of column used to order rows
@ 2003-03-19 16:18  cliff@cliffmeyers.com
  0 siblings, 1 reply; 2+ messages in thread

From: cliff@cliffmeyers.com @ 2003-03-19 16:18 UTC (permalink / raw)
  To: pgsql-sql

hello,


I was hoping someone might be able to help me with this problem...

I have a table that is essentially joined back to a parent table.  I have a
column in the child table called "rank" that is a simple integer data type, used
to indicate the order that the child elements should be displayed in.

What I want to be able to do is make sure that at all times the child records
linked to a parent record have values for the "rank" field that are consecutive
starting at 1, ie (1,2,4,5,6) not (1,2,4,5,7).

Can someone offer the best suggestion to do this?  

Should I write a PL/pgsql function and add it as a column constraint to check to
make sure the numbers are consecutive?

Can I use some kind of trigger that will execute a function and "automatically"
give the fields the correct number?  Would this seriously impact the performance
since it would have to go through what might become a large table only to work on
a small part of it (ie, records with the same parent_id)?

I have a lot of experience with mySQL but a lot of these more sophisticated pgSQL
features are a little tough for me to get a handle on immediately... thanks very
much.


-Cliff



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

* Re: integrity of column used to order rows
@ 2003-03-19 16:50  Ed L. <pgsql@bluepolka.net>
  parent: cliff@cliffmeyers.com
  0 siblings, 0 replies; 2+ messages in thread

From: Ed L. @ 2003-03-19 16:50 UTC (permalink / raw)
  To: cliff@cliffmeyers.com; pgsql-sql

On Wednesday March 19 2003 9:18, cliff@cliffmeyers.com wrote:
>
> What I want to be able to do is make sure that at all times the child
> records linked to a parent record have values for the "rank" field that
> are consecutive starting at 1, ie (1,2,4,5,6) not (1,2,4,5,7).
>
> Can someone offer the best suggestion to do this?

This is certainly possible via a triggered PL/pgSQL function.  Of course, 
whether or not it is feasible w/r/t performance or exactly how you choose 
to set the ranks depends on your context.  If you have appropriate indices 
on the foreign key to the parent table, the overall size of the table is 
probably irrelevant; use of the index would allow you to avoid traversal of 
the entire table.  You also have the option of writing the function in C to 
get better performance.

Ed





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

end of thread, other threads:[~2003-03-19 16:50 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-19 16:18 integrity of column used to order rows cliff@cliffmeyers.com
2003-03-19 16:50 ` Ed L. <pgsql@bluepolka.net>

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