* Пример на диалекте 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)
SQL Справочник v0.05 © 2007-2025 Igor Salnikov aka SunDoctor