Re: [SQL] Question about SQL Control Structure(if then, for loop)
If you're control is that simple, you can write similar statements in pure SQL: RDM=# for i in 1 .. 10 loopRDM-# select "test"RDM-# end loop;ERROR: syntax error at or near "for" at character 1LINE 1: for i in 1 .. 10 loop SELECT 'test' FROM RDM=# if exits ( select * from testtable)RDM-# thenRDM-# select "TEST" RDM-# ;ERROR: syntax error at or near "if" at character 1LINE 1: if exits ( select * from testtable)^ SELECT 'test' FROM testtable LIMIT 1;Without knowing about what you want to do, I can't guarantee that that will suffice and/or be efficient. If it gets much more complicated, you might have to go to some procedural language (PL/PGSQL, PL/Perl, etc). Just remember that SQL is set-based, not procedural. -Mike
Re: [SQL] Question about SQL Control Structure(if then, for loop)
SELECT 'test' FROM SELECT 'test' FROM generate_series(1,10);Sorry, thought faster than I could type :)-Mike
[SQL] Storing an ordered list
What is the best way to store and ordered list that can be updated OLTP-style? A simplified problem is that I have an event, and the event has an ordered list of predicates and I need to preserve the order of the predicates. All of the data is entered via a web application, and I would like to support the new flashy ajax drag-droppy thingies, meaning that there could be a significant amount of updates if the user is dragging things all over the place. I figure that one choice is to explicitly code the order as an integer column in the predicate table which has the advantage of being very easy and fast to query/order but *very* slow to reorder as all of the predicates need to be updated. This would seem to be a postgres/MVCC weak spot as well. Example: create table event (event_id integer); create table predicate (event_id integer not null references event(event_id), name varchar, order integer); insert into event (event_id) values (1); insert into predicate (1, 'first event', 1); insert into predicate (1, 'second predicate', 2); select * from predicate p where p.event_id = 1 order by p.order; I'm also thinking about a linked list, i.e. create table event (event_id integer); create table predicate (predicate_id integer, event_id integer not null references event(event_id), name varchar, next_predicate integer references predicate (predicate_id)); insert into predicate (101, 1, 'second predicate', NULL); insert into predicate (102, 1, 'first predicate', 101); The downside is that I'm not quite sure how to efficiently query the linked list. Any suggestions? Are there any known best practices for storing ordered lists in relational databases? Are there any tricks that I can use with postgres? Thanks -Mike ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Storing an ordered list
On 7/26/06, Aaron Bono <[EMAIL PROTECTED]> wrote: If you use the linked list, remember this: to reduce the updates you are going to need more code in the application as it will have to keep track of what to update and what to not update. It will also be more difficult to order the items using SQL so your application may have to take on that burden. As a result, your application will become more complicated and writing reports that use the ordering will become difficult. Yeah, that was what I wanted to avoid. I wasn't sure if there was a common approach to this sort of problem. When I need something like this I go with your first approach, a simple order field. Unless the user is reordering a small number of items in a very large list and doing it frequently, is there really a need to worry about the number of updates? Are you worrying about a performance problem you will never have? Perhaps it is unnecessary, however I wanted to start out with a decent design that could scale if need be. Also, I'd like to support "real-time" reordering, i.e. the user won't have to click "save" ... as soon as they drag the item to a new position the list is updated. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Storing an ordered list
On 7/26/06, Bruno Wolff III <[EMAIL PROTECTED]> wrote: If you use numeric instead of int, then it is easy to insert new values. Hmm, hadn't thought about that. How would you normally implement it? I'm thinking that, if I wanted to insert between A and B, I could take (A.order + B.order)/2, which would be pretty simple. Is there a better way? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
