CODEWITHSUNDEEP
postgresqlcalculated-columns
calebeaires
calebeaires

Reputation: 2084

Create a PostgreSQL function that becomes a formula field of a table retrieving related data from other table

The example above can be done on a SQL Server. It is a function that performs the calculation on another table while getting the current table field Id to list data from other table, return a single value.

Question: how to do the exact thing with 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         |
+-----------+------------------------+---------------+------------+-----------+

The function

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

Now, (at SQL Server), after update the first table fields with ALTER TABLE Artists ADD AlbumCount AS dbo.ufn_AlbumCount(ArtistId); whe can list and get the following result.

+------------+------------------+--------------+-------------+--------------+
| 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            |
+------------+------------------+--------------+-------------+--------------+

but how to achieve this on postgresql?

Upvotes: 0

Views: 1171

Answers (1)

user330315
user330315

Reputation:

Postgres doesn't support "virtual" computed column (i.e. computed columns that are generated at runtime), so there is no exact equivalent. The most efficient solution is a view that counts this:

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;

Another option is to create a function that can be used as a "virtual column" in a select - but as this is done row-by-row, this will be substantially slower than the view.

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;

Then you can include this as a column:

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

Using the function like that, requires to prefix the function reference with the table alias (alternatively, you can use album_count(a))

Online example

Upvotes: 2

Related Questions