Doing multiple queries in Postgresql - conditional loop

Question

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

Please add the table definitions and some sample data and the expected output from that sample data — user330315
– user330315, Commented Feb 20, 2014 at 10:02
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
– shevy, Commented Feb 24, 2014 at 9:06
That's definitely a case for a recursive query: postgresql.org/docs/current/static/queries-with.html — user330315
– user330315, Commented Feb 24, 2014 at 9:43

Ivan Voras · Accepted Answer · 2014-02-20 10:17:26Z

2

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 .

answered Feb 20, 2014 at 10:17

Ivan Voras

2,0302 gold badges14 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bernhard Barker Over a year ago

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.

shevy Over a year ago

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.

Collectives™ on Stack Overflow

Doing multiple queries in Postgresql - conditional loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related