media_files is a table, I'm trying to make this function work:
CREATE OR REPLACE FUNCTION pick_artist(media_files)
RETURNS varchar AS $$
BEGIN
IF $1.album_artist IS NULL THEN RETURN $1.album;
ELSIF $1.album_artist = '' THEN RETURN $1.album;
ELSE RETURN $1.album_artist;
END IF;
END;
$$ LANGUAGE plpgsql;
]
in this query:
SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW))
FROM (
SELECT
MAX(MD5(CONCAT(album, release_year, artist))) AS token,
album,
MAX(release_year) AS release_year,
MAX(artwork_path) AS artwork,
pick_artist(ROW(album_artist, artist)) AS artist,
ARRAY_AGG((token,artist,album)::media_file_detail) as media_files
FROM media_files
INNER JOIN playlist_media_files ON playlist_media_files.media_file_id = media_files.id
WHERE playlist_media_files.playlist_id = 1
GROUP BY artist, release_year, album
ORDER BY artist, release_year, album
) as ALBUM_ROW;
The table media_files has both `artist` and `album_artist`, I need to pick up one of the two through the function and make sure that `artist` is returned for the final JSON document.
I did try many things, read up documentation, ROW, RECORD, SETOF, composite input and return types but it seems I cannot find a way.
Also if the scanner sees `album_artist` in the query (even if it's part of the function parameters) it complains that it's not part of the GROUP BY (hence the ROW() try).
Does anyone have any suggestion how this problem can be solved?