查询返回确切的行数 [英] Query returning exact number of rows

问题描述

我有一个存储两个外键的表，实现了n:m关系.

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

其中一个指向一个人(subject)，另一个指向一个特定的项目.
现在，在一个不同的表中指定了一个人可能拥有的商品数量，我需要一个查询，该查询将返回与一个人可能拥有的商品数量相同的行数.

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.

其余记录可以用NULL值或其他任何值填充.

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.

例子

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
)

我需要一个查询/函数，该查询/函数将返回所有主题项目(该主题可能有5个项目) 但只有3个项目分配给该主题.

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.

返回有点像:

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

我正在使用postgresql-8.3

I am using postgresql-8.3

推荐答案

考虑此 plpgsql函数的很大程度上简化版本.应该可以在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;

手册中有关 plpgsql的详细信息(链接至8.3版)

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

这篇关于查询返回确切的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！