CODEWITHSUNDEEP
sqlpostgresqlplpgsql
soupdiver
soupdiver

Reputation: 3683

Storing results in a variable in plpgsql

for my homework I've have the following task: I've got a simple database which is like facebook with friends and their relations. Now I have to count the number of direct friends and the number of the friends of the direct friends.

So my approach was: Selecting all direct friends and store their ids's in a variable. Then I iterate over these ids and store all direct friends of the current user in another variable. (For each in the same). In the end I've got a big array with alle the related user id's. Then I just have to select distinct the id's and count them.

My Problem is: How do I store the id's in a variable (array?) so that I can easily loop over them and in the end do a distinct select of the id's?

Hope you can give me a hint on that...

Upvotes: 0

Views: 400

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658707

If the assignment does not require the use of plpgsql, it can be solved more easily with plain SQL.

Given this table (which you should have provided):

CREATE TEMP TABLE f (
   friend_of int
  ,friend_with int
  ,PRIMARY KEY (friend_of, friend_with)
);

How many friends does user 1 have?

SELECT count(*) As friends
FROM   f
WHERE  friend_of = 1;

How many unique friends do the friends of user 1 have (excluding user 1 himself)?

SELECT count(DISTINCT f2.friend_with) - 1 AS friends_friends -- minus himself
FROM   f f1
JOIN   f f2 ON f2.friend_of = f1.friend_with
WHERE  f1.friend_of = 1;

Without DISTINCT common friends would be counted multiple times.
Demo on sqlfiddle.

Upvotes: 3

Related Questions