Re: manipulate and return row inside a function

Hello,

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?

Thanks.