pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Masaru Sugawara <rk73@echna.ne.jp>
To: Richard Emberson <emberson@phc.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Finding matching columns from two tables
Date: Sat, 23 Feb 2002 17:41:44 +0900
Message-ID: <20020223173857.DE5D.RK73@echna.ne.jp> (raw)
In-Reply-To: <3C757613.84BA73EB@phc.net>
References: <3C757613.84BA73EB@phc.net>

On Thu, 21 Feb 2002 14:34:59 -0800
Richard Emberson <emberson@phc.net> wrote:

> Below can be included in a psql via the \i command.

 I have lost sight of this mail -- especially detailed explanation annotated
 on it. Judging from these examples, my previous queries wouldn't work correctly.
 Anyway I have made several modifications on them. 


 select org2.group_set_id 
  from (select org0.group_id, org0.group_set_id
          from organizations as org0
         where exists
                 (select mem0.group_id
                    from membership as mem0
                   where org0.group_id = mem0.group_id
                         and mem0.user_id = <<input parameter>>
                  )
               and not exists
                  (select org1.group_id, org1.group_set_id
                     from organizations as org1
                    where not exists
                            (select mem1.group_id
                               from membership as mem1
                              where org1.group_id = mem1.group_id
                                    and mem1.user_id =  <<input parameter>>
                             )
                          and org0.group_set_id = org1.group_set_id
                   )
        ) as org2
 group by org2.group_set_id
 ;


Regards,
Masaru Sugawara

> ----------------------------------------------------------------
> /*
> In some cases, the group_ids associated with a group_set_id are the
> same as the  group_ids associated with a user_id. For a given user_id
> determine if there is a corresponding group_set_id.
> 
> Find the group_set_id such that for a given user_id (input parameter)
> there is a one-to-one correspondence between the group_ids associated
> with the user_id and the group_ids associated with the group_set_id;
> for every group_id that the user_id has, the group_set_id also has it
> and for every group_id that the group_set_id has, the user_id also has
> it.
> If there is no such group_set_id, then return null.
> 
> What query will generate the group_set_id?
> 
> The function get_group_set_id() is my attempt. Is there a better way?
> The fact that it has 4 SELECT statement in it is, I expect, far from
> optimum.
> 
> SELECT get_group_set_id(10);  -- should be 30
> SELECT get_group_set_id(11);  -- should be -1
> SELECT get_group_set_id(12);  -- should be -1
> SELECT get_group_set_id(13);  -- should be 31
> */
> 
> -- all of the users
> -- user_id are unique
> DROP TABLE users;
> CREATE TABLE users (
>     user_id             BIGINT,
>     -- other columns
>     PRIMARY KEY (user_id)
> );
> COPY users FROM stdin USING DELIMITERS ':';
> 10
> 11
> 12
> 13
> \.
> 
> -- all of the groups
> -- user_id are unique
> DROP TABLE groups;
> CREATE TABLE groups (
>     group_id             BIGINT,
>     -- other columns
>     PRIMARY KEY (group_id)
> );
> COPY groups FROM stdin USING DELIMITERS ':';
> 20
> 21
> 22
> 23
> 24
> \.
> -- a user can be a member of one or more groups
> DROP TABLE membership;
> CREATE TABLE membership (
>     user_id             BIGINT,
>     group_id            BIGINT,
>     -- other columns
>     FOREIGN KEY (user_id) REFERENCES users (user_id),
>     FOREIGN KEY (group_id) REFERENCES groups (group_id)
> );
> COPY membership FROM stdin USING DELIMITERS ':';
> 10:20
> 10:21
> 11:20
> 12:21
> 12:22
> 12:23
> 13:20
> 13:22
> \.
> 
> -- a group set is a set of one or more groups
> DROP TABLE group_sets;
> CREATE TABLE group_sets (
>     group_set_id        BIGINT,
>     group_id            BIGINT,
>     -- other columns
>     FOREIGN KEY (group_id) REFERENCES groups (group_id)
>     -- FOREIGN KEY (group_set_id) REFERENCES group_sets (group_set_id)
> );
> COPY group_sets FROM stdin USING DELIMITERS ':';
> 30:20
> 30:21
> 31:20
> 31:22
> 32:20
> 32:21
> 32:22
> 32:24
> \.CREATE OR REPLACE FUNCTION get_group_set_id (
> BIGINT
> )
> RETURNS BIGINT AS '
> DECLARE
>     -- parameters
>     user_id_p ALIAS FOR $1;
>     -- local variables
>     group_id_count_v INTEGER;
>     count_v INTEGER;
>     group_set_id_v BIGINT;
>     foo group_sets%ROWTYPE;
> BEGIN
>     -- how many groups were given to this user
>     SELECT INTO group_id_count_v count(*) FROM membership
>         WHERE user_id = user_id_p;
> 
>     -- select all that have groups shared by the linked user and
>     -- have the right number (or more)
>     FOR foo IN SELECT group_set_id FROM group_sets
>     WHERE group_id IN
>         (SELECT group_id FROM membership
>                 WHERE user_id = user_id_p)
>     GROUP BY group_set_id
> 
>     HAVING count(*) = group_id_count_v
>     LOOP
> 
>         -- make sure it has only the right number of group_ids
>         SELECT INTO count_v count(*) FROM group_sets
>             WHERE group_set_id = foo.group_set_id;
> 
>         IF group_id_count_v = count_v THEN
>             RETURN foo.group_set_id;
>         END IF;
> 
>     END LOOP;
> 
>     RETURN -1;
> END;
> ' LANGUAGE 'plpgsql';
> ----------------------------------------------------------------
> 





view thread (3+ messages)

Message-ID: <20020223173857.DE5D.RK73@echna.ne.jp>
Permalink:  ../20020223173857.DE5D.RK73@echna.ne.jp/
Also on:    postgresql.org/message-id/20020223173857.DE5D.RK73@echna.ne.jp

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: rk73@echna.ne.jp, emberson@phc.net
  Subject: Re: Finding matching columns from two tables
  In-Reply-To: <20020223173857.DE5D.RK73@echna.ne.jp>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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