Query returning exact number of rows

Question

I have a table that stores two foreign keys, implementing a n:m relationship.

One of them points to a person (subject), the other one to a specific item.
Now, the amount of items a person may have is specified in a different table and I need a query which would return the same number of rows as the number of items a person may have.

The rest of the records may be filled with NULL values or whatever else.

It has proven to be a pain to solve this problem from the application side, so I've decided to try a different approach.

Edit: Example

CREATE TABLE subject_items
(
  sub_item integer NOT NULL,
  sal_subject integer NOT NULL,
  CONSTRAINT pkey PRIMARY KEY (sub_item, sal_subject),
  CONSTRAINT fk1 FOREIGN KEY (sal_subject)
      REFERENCES subject (sub_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk2 FOREIGN KEY (sub_item)
      REFERENCES item (item_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

I need a query/function which would return all subject items (subject may have 5 items) but there are only 3 items assigned to the subject.

Return would be somewhat like:

sub_item   |  sal_subject
2          |   1
3          |   1
4          |   1
NULL       |   1
NULL       |   1

I am using postgresql-8.3

Answer 1

Could work like this (pure SQL solution):

SELECT a.sal_subject
     , b.sub_item
FROM  (
    SELECT generate_series(1, max_items) AS rn
         , sal_subject
    FROM   subject
    ) a
LEFT   JOIN (
    SELECT row_number() OVER (PARTITION BY sal_subject ORDER BY sub_item) AS rn
         , sal_subject
         , sub_item
    FROM   subject_items
    ) b USING (sal_subject, rn)
ORDER  BY sal_subject, rn

1. Generate the maximum rows per subject, let's call them theoretical items.
   See the manual for generate_series().
2. Apply a row-number to existing items per subject.
   Manual about window functions.
3. LEFT JOIN the existing items to the theoretical items per subject. Missing items are filled in with NULL.

In addition to the table you disclosed in the question, I assume a column that holds the maximum number of items in the subject table:

CREATE temp TABLE subject
( sal_subject integer,     -- primary key of subject
  max_items int);          -- max. number of items

---

Query for PostgreSQL 8.3, substituting for the missing window function row_number():

SELECT a.sal_subject
     , b.sub_item
FROM  (
    SELECT generate_series(1, max_items) AS rn
         , sal_subject
    FROM   subject
    ) a
LEFT   JOIN (
    SELECT rn, sal_subject, arr[rn] AS sub_item
    FROM  (
        SELECT generate_series(1, ct) rn, sal_subject, arr
        FROM  (
            SELECT s.sal_subject
                 , s.ct
                 , ARRAY(
                        SELECT sub_item
                        FROM   subject_items s0
                        WHERE  s0.sal_subject = s.sal_subject
                        ORDER  BY sub_item
                    ) AS arr
            FROM  (
                SELECT sal_subject
                     , count(*) AS ct
                FROM   subject_items
                GROUP  BY 1
                ) s
            ) x
        ) y
    ) b USING (sal_subject, rn)
ORDER  BY sal_subject, rn

More about substituting row_number() in this article by Quassnoi.

Answer 2

Consider this largely simplified version of your plpgsql function. Should work in PostgreSQL 8.3:

CREATE OR REPLACE FUNCTION x.fnk_abonemento_nariai(_prm_item integer)
  RETURNS SETOF subject_items AS
$BODY$
DECLARE
    _kiek    integer :=  num_records    -- get number at declaration time
                         FROM subjekto_abonementai WHERE num_id = _prm_item;
    _counter integer;
BEGIN

RETURN QUERY                            -- get the records that actualy exist
SELECT sub_item, sal_subject
FROM   sal_subject 
WHERE  sub_item = prm_item;

GET DIAGNOSTICS _counter = ROW_COUNT;   -- save number of returned rows.

RETURN QUERY
SELECT NULL, NULL                       -- fill the rest with null values
FROM   generate_series(_counter + 1, _kiek);

END;
$BODY$ LANGUAGE plpgsql VOLATILE STRICT;

Details about plpgsql in the manual (link to version 8.3).

Answer 3

I was able to come up to this simplistic solution: First returning all the values i may select then looping returning null values while we have the right amount. Posting it here if someone would stumble on the same problem. Still looking for easier/faster solutions if they exist.

CREATE OR REPLACE FUNCTION fnk_abonemento_nariai(prm_item integer)
  RETURNS SETOF subject_items AS
$BODY$DECLARE _kiek integer;
DECLARE _rec subject_items;
DECLARE _counter integer;
BEGIN
  /*get the number of records we need*/
  SELECT INTO _kiek num_records
  FROM subjekto_abonementai
    WHERE num_id = prm_item;

  /*get the records that actualy exist */

  FOR _rec IN SELECT sub_item, sal_subject
      FROM sal_subject 
      WHERE sub_item = prm_item LOOP
    return 
      next _rec;
    _counter := COALESCE(_counter, 0) + 1;
  END LOOP;

  /*fill the rest with null values*/

  While _kiek > _counter loop
    _rec.sub_item := NULL;
    _rec.sal_subject := NULL;
    Return next _rec;
    _counter := COALESCE(_counter, 0) + 1;
  end loop;

END;$BODY$
  LANGUAGE plpgsql VOLATILE;