时间戳

首页 > 解决方案 > 创建一个 PostgreSQL 函数，该函数成为一个表的公式字段，从其他表中检索相关数据

postgresql - 创建一个 PostgreSQL 函数，该函数成为一个表的公式字段，从其他表中检索相关数据

问题描述

上面的示例可以在 SQL Server 上完成。它是一个函数，在另一个表上执行计算，同时获取当前表字段 Id 以列出来自其他表的数据，返回单个值。

问题：如何用 PostgreSQL 做确切的事情


SELECT TOP(5) * FROM Artists;

+------------+------------------+--------------+-------------+
| ArtistId   | ArtistName       | ActiveFrom   | CountryId   |
|------------+------------------+--------------+-------------|
| 1          | Iron Maiden      | 1975-12-25   | 3           |
| 2          | AC/DC            | 1973-01-11   | 2           |
| 3          | Allan Holdsworth | 1969-01-01   | 3           |
| 4          | Buddy Rich       | 1919-01-01   | 6           |
| 5          | Devin Townsend   | 1993-01-01   | 8           |
+------------+------------------+--------------+-------------+


SELECT TOP(5) * FROM Albums;

+-----------+------------------------+---------------+------------+-----------+
| AlbumId   | AlbumName              | ReleaseDate   | ArtistId   | GenreId   |
|-----------+------------------------+---------------+------------+-----------|
| 1         | Powerslave             | 1984-09-03    | 1          | 1         |
| 2         | Powerage               | 1978-05-05    | 2          | 1         |
| 3         | Singing Down the Lane  | 1956-01-01    | 6          | 3         |
| 4         | Ziltoid the Omniscient | 2007-05-21    | 5          | 1         |
| 5         | Casualties of Cool     | 2014-05-14    | 5          | 1         |
+-----------+------------------------+---------------+------------+-----------+

功能

CREATE FUNCTION [dbo].[ufn_AlbumCount] (@ArtistId int)  
RETURNS smallint
AS  
BEGIN  
    DECLARE @AlbumCount int;
    SELECT @AlbumCount = COUNT(AlbumId)
    FROM Albums
    WHERE ArtistId = @ArtistId; 
    RETURN @AlbumCount;
END;
GO

现在，（在 SQL Server 上），在使用ALTER TABLE Artists ADD AlbumCount AS dbo.ufn_AlbumCount(ArtistId);whe 更新第一个表字段后，可以列出并获得以下结果。

+------------+------------------+--------------+-------------+--------------+
| ArtistId   | ArtistName       | ActiveFrom   | CountryId   | AlbumCount   |
|------------+------------------+--------------+-------------+--------------|
| 1          | Iron Maiden      | 1975-12-25   | 3           | 5            |
| 2          | AC/DC            | 1973-01-11   | 2           | 3            |
| 3          | Allan Holdsworth | 1969-01-01   | 3           | 2            |
| 4          | Buddy Rich       | 1919-01-01   | 6           | 1            |
| 5          | Devin Townsend   | 1993-01-01   | 8           | 3            |
| 6          | Jim Reeves       | 1948-01-01   | 6           | 1            |
| 7          | Tom Jones        | 1963-01-01   | 4           | 3            |
| 8          | Maroon 5         | 1994-01-01   | 6           | 0            |
| 9          | The Script       | 2001-01-01   | 5           | 1            |
| 10         | Lit              | 1988-06-26   | 6           | 0            |
+------------+------------------+--------------+-------------+--------------+

但是如何在 postgresql 上实现这一点？

标签： postgresqlcalculated-columns

解决方案

Postgres 不支持“虚拟”计算列（即在运行时生成的计算列），因此没有完全等价的。最有效的解决方案是考虑到这一点的视图：

create view artists_with_counts
as
select a.*, 
       coalesce(t.album_count, 0) as album_count
from artists a
  left join (
     select artist_id, count(*) as album_count
     from albums
     group by artist_id
  ) t on a.artist_id = t.artist_id;

另一种选择是创建一个可以在选择中用作“虚拟列”的函数 - 但由于这是逐行完成的，这将比视图慢得多。

create function album_count(p_artist artists)
  returns bigint
as
$$
  select count(*)
  from albums a
  where a.artist_id = p_artist.artist_id;
$$
language sql
stable;

然后您可以将其包含为一列：

select a.*, a.album_count
from artists a;

使用这样的函数，需要在函数引用前加上表别名（或者，您可以使用album_count(a)）

在线示例

推荐阅读