current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

Let me first start by stating that in the last two weeks I have received ENORMOUS help from just about all of you (ok ok not all... but I think perhaps two dozen people commented, and almost all of these comments were helpful). This is really amazing and I think it shows that the stackoverflow team really did something GREAT altogether. So thanks to all!

Now as some of you know, I am working at a campus right now and I have to use a windows machine. (I am the only one who has to use windows here... :( )

Now I manage to setup (ok, IT department did that for me) and populate a Postgres database (this I did on my own), with about 400 mb of data. Which perhaps is not so much for most of you heavy Ppostgre users, but I was more used to sqlite database for personal use which rarely exceeded 2mb ever.

Anyway, sorry for being so chatty - now the queries from that database work nicely. I use ruby to do queries actually.

The entries in the Postgres database are interconnected, in as far as they are like "pointers" - they have one field that points to another field.

Example: entry 3667 points to entry 35785 which points to entry 15566. So it is quite simple.

The main entry is 1, so the end of all these queries is 1. So, from any other number, we can reach 1 in the end as the last result.

I am using ruby to make as many individual queries to the database until the last result returned is 1. This can take up to 10 individual queries. I do this by logging into psql with my password and data, and then performing the SQL query via -c. This probably is not ideal, it takes a little time to do these logins and queries, and ideally I would have to login only once, perform ALL queries in Postgres, then exit with a result (all these entries as result).

Now here comes my question: - Is there a way to make conditional queries all inside of Postgres?

I know how to do it in a shell script and in ruby but I do not know if this is available in postgresql at all.

I would need to make the query, in literal english, like so:

"Please give me all the entries that point to the parent entry, until the last found entry is eventually 1, then return all of these entries."

I already "solved" it by using ruby to make several queries until 1 is eventually returned, but this strikes me as fairly inelegant and possibly not effective.

Any information is very much appreciated - thanks!

Edit (argh, I fail at pasting...):

Example dataset, the table would be like this:

 id |       parent
----+---------------+
  1 |             1 |
  2 |        131567 |
  6 |        335928 |
  7 |             6 |
  9 |             1 |
 10 |        135621 |
 11 |             9 |

I hope that works, I tried to narrow it down solely on example.

For instance, id 11 points to id 9, and id 9 points to id 1.

It would be great if one could use SQL to return: 11 -> 9 -> 1

share|improve this question
2  
Please add the table definitions and some sample data and the expected output from that sample data –  a_horse_with_no_name Feb 20 '14 at 10:02
    
Ok let's see: hmm ... –  shevy Feb 20 '14 at 13:25
    
I added an example table above - of course it is much simpler than the real database table (which has around 240 MB in size). –  shevy Feb 24 '14 at 9:06
    
That's definitely a case for a recursive query: postgresql.org/docs/current/static/queries-with.html –  a_horse_with_no_name Feb 24 '14 at 9:43

1 Answer 1

up vote 2 down vote

Unless you give some example table definitions, what you're asking for vaguely reminds of a tree structure which could be manipulated with recursive queries: http://www.postgresql.org/docs/8.4/static/queries-with.html .

share|improve this answer
    
This answer appears to consist mainly of a link. It would be preferable to include the essential parts of the answer here, and just provide the link for additional reference. If you're not up to this task, you should consider simply leaving a comment on the question instead of posting an answer. –  Dukeling Feb 22 '14 at 3:37
    
I added an example table definition above. The table has two columns, called id and parent. The result of each id is a parent-id, that points to another id, which can point to another id, until the main id 1 is reached. This is very simple in ruby, it is just a hash basically. I am unsure if this can be done in postgresql. –  shevy Feb 24 '14 at 9:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.