pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Emberson <emberson@phc.net>
To: pgsql-sql@postgresql.org
Subject: Finding matching columns from two tables
Date: Thu, 21 Feb 2002 12:17:08 -0800
Message-ID: <3C7555C4.95A1907C@phc.net> (raw)

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

There are basically two tables (and some others for ref-int): membership
and group_sets.
A user (user_id) is a member of one or more groups (group_id). Groups
are also organized into
group sets (group_set_id) in the group_sets table. The task is to find
the group_set_id for a set
of groups, if it exists, given a user_id, and the associated groups that
user is a member of.

The function, get_group_set_id(),  at the end is my attempt. It works
but its rather convoluted and
requires 4 SELECT statements.

Any help.

Thanks.

Richard

----------------------------------------------------------------
/*
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)  latest in thread

Message-ID: <3C7555C4.95A1907C@phc.net>
Permalink:  ../3C7555C4.95A1907C@phc.net/
Also on:    postgresql.org/message-id/3C7555C4.95A1907C@phc.net

 · 

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: emberson@phc.net
  Subject: Re: Finding matching columns from two tables
  In-Reply-To: <3C7555C4.95A1907C@phc.net>

* 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