Postgres: Возврат нетипизированного списка


* Пример на диалекте sql

CREATE OR REPLACE FUNCTION client.fx_user_stat(active_flag integer)
  RETURNS SETOF record AS
$BODY$

SELECT user_id, user_name, count(*) as x FROM client.users
    WHERE user_active=$1 GROUP BY user_id, user_name

$BODY$
  LANGUAGE 'sql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION client.fx_user_stat(integer) OWNER TO sk4_admin;
GRANT EXECUTE ON FUNCTION client.fx_user_stat(integer) TO public;
GRANT EXECUTE ON FUNCTION client.fx_user_stat(integer) TO sk4_admin;

* Пример на диалекте plpgsql

CREATE OR REPLACE FUNCTION core.fx_get_album_cloud(row_limit integer)
  RETURNS SETOF record AS
$BODY$
--
-- SELECT * FROM core.fx_get_album_cloud(20) AS (name varchar, weight bigint)
--
DECLARE
    retRecord RECORD;
BEGIN
    FOR retRecord in
        SELECT genre_name, count(*) as weight FROM core.genres
        INNER JOIN core.genres_albums ON genre_id=ga_genre_id
        GROUP BY genre_name ORDER BY 2 DESC LIMIT row_limit
    LOOP
        RETURN NEXT retRecord;
    END LOOP;
    RETURN;
END;
--
-- eof
--
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100
  ROWS 1000;
ALTER FUNCTION core.fx_get_album_cloud(integer) OWNER TO sk4_admin;
GRANT EXECUTE ON FUNCTION core.fx_get_album_cloud(integer) TO sk4_admin;
GRANT EXECUTE ON FUNCTION core.fx_get_album_cloud(integer) TO public;

* Выборка из нетипизированных списков делается так

SELECT * FROM client.fx_user_stat(1) AS (f1 int, f2 varchar, f3 bigint)