Обсуждение: manipulate and return row inside a function

Поиск
Список
Период
Сортировка

manipulate and return row inside a function

От
Claudio Poli
Дата:
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.

Re: manipulate and return row inside a function

От
Claudio Poli
Дата:
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@gmail.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.

Re: manipulate and return row inside a function

От
Luca Ferrari
Дата:
On Thu, Nov 14, 2013 at 8:50 AM, Claudio Poli <masterkain@gmail.com> wrote:
> 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;
>
>

This works fine for me:
select * from pick_artist( ROW('a','b') );
on 9.3.
I don't know what is complaining about in your case.

Luca

Re: manipulate and return row inside a function

От
Claudio Poli
Дата:
I solved it with using COALESCE.

Best,
c.


On Thu, Nov 14, 2013 at 9:03 AM, Luca Ferrari <fluca1978@infinito.it> wrote:
On Thu, Nov 14, 2013 at 8:50 AM, Claudio Poli <masterkain@gmail.com> wrote:
> 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;
>
>

This works fine for me:
select * from pick_artist( ROW('a','b') );
on 9.3.
I don't know what is complaining about in your case.

Luca