Re: manipulate and return row inside a function

some other things I'm trying:

SELECT
pick_artist(album_artist, artist) AS artist,

CREATE OR REPLACE FUNCTION pick_artist(album_artist varchar, album
varchar)
RETURNS varchar AS $$
BEGIN
IF album_artist IS NULL THEN RETURN album;
ELSIF album_artist = '' THEN RETURN album;
ELSE RETURN album_artist;
END IF;
END;
$$ LANGUAGE plpgsql;

On Thu, Nov 14, 2013 at 8:38 AM, Claudio Poli <masterkain(at)gmail(dot)com> wrote:

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