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
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).