manipulate and return row inside a function

From: Claudio Poli <masterkain(at)gmail(dot)com>
To: pgsql novice <pgsql-novice(at)postgresql(dot)org>
Subject: manipulate and return row inside a function
Date: 2013-11-14 07:38:20
Message-ID: CANp6QoKeOkEJssMy7UnXyviXXkNeA8MnVjhgHFwu0MmRi-msqQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Claudio Poli 2013-11-14 07:50:34 Re: manipulate and return row inside a function
Previous Message Claudio Poli 2013-11-13 17:51:57 Re: How to alias attributes in an ARRAY_AGG expression