Configure pgAdmin greencity functions

Execute the following SQL-scripts to add functions in your Postgres database

1. Function fn_recommended_econews_by_opened_eco_news

---

CREATE OR REPLACE FUNCTION public.fn_recommended_econews_by_opened_eco_news(
	current_eco_news_id bigint)
    RETURNS TABLE(id bigint, title character varying, text character varying, creation_date timestamp with time zone, image_path character varying, author_id bigint, source character varying, news_rating bigint) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
BEGIN

RETURN QUERY

WITH recomendet_news AS (
SELECT uniq_ent.*,
	   count(tags_id) OVER(PARTITION BY eco_news_id) AS news_rating
FROM (SELECT DISTINCT ent.* FROM eco_news_tags AS ent) AS uniq_ent

	WHERE uniq_ent.tags_id IN (SELECT t.id FROM eco_news_tags AS ent
				  				JOIN tags AS t ON t.id = ent.tags_id
				  				WHERE ent.eco_news_id = current_eco_news_id)
UNION

SELECT uniq_ent.*,
	   0 AS news_rating
FROM (SELECT DISTINCT ent.* FROM eco_news_tags AS ent) AS uniq_ent

	WHERE uniq_ent.tags_id NOT IN (SELECT t.id FROM eco_news_tags AS ent
				  				JOIN tags AS t ON t.id = ent.tags_id
				  				WHERE ent.eco_news_id = current_eco_news_id)
)

 	SELECT DISTINCT en.id, en.title, en.text, en.creation_date,
					en.image_path, en.author_id, en.source, ren.news_rating
		FROM recomendet_news AS ren
		JOIN eco_news AS en ON en.id = ren.eco_news_id
			WHERE en.id <> current_eco_news_id
		ORDER BY ren.news_rating DESC, en.creation_date DESC, en.id
		LIMIT 3;

END $BODY$;

ALTER FUNCTION public.fn_recommended_econews_by_opened_eco_news(bigint)
    OWNER TO greencity_admin;

2. Function fn_textsearcheconews

---

CREATE OR REPLACE FUNCTION public.fn_textsearcheconews(
	search_phrase text)
    RETURNS TABLE(id bigint, creation_date timestamp with time zone, image_path character varying, author_id bigint, text character varying, title character varying, source character varying) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$

	
BEGIN 
	RETURN QUERY
	SELECT vn.id, vn.creation_date, vn.image_path, vn.author_id, vn.text, vn.title, vn.source 
	FROM VW_EcoNewsWithTags AS vn 
	WHERE vector @@ to_tsquery('simple',  REPLACE(REPLACE(search_phrase, ' '  , '|' ),'||', '|'))
	ORDER BY ts_rank(vector, plainto_tsquery('simple',  REPLACE(REPLACE(search_phrase, ' '  , '|' ),'||', '|'))) DESC;

END
$BODY$;

ALTER FUNCTION public.fn_textsearcheconews(text)
    OWNER TO greencity_admin;

3. Function pg_buffercache_pages

---

CREATE OR REPLACE FUNCTION public.pg_buffercache_pages(
	)
    RETURNS SETOF record 
    LANGUAGE 'c'
    COST 1
    VOLATILE 
    ROWS 1000
AS '$libdir/pg_buffercache', 'pg_buffercache_pages'
;

ALTER FUNCTION public.pg_buffercache_pages()
    OWNER TO azure_superuser;

GRANT EXECUTE ON FUNCTION public.pg_buffercache_pages() TO azure_superuser;

GRANT EXECUTE ON FUNCTION public.pg_buffercache_pages() TO azure_pg_admin;

REVOKE ALL ON FUNCTION public.pg_buffercache_pages() FROM PUBLIC;

4. Function pg_stat_statements

---

CREATE OR REPLACE FUNCTION public.pg_stat_statements(
	showtext boolean,
	OUT userid oid,
	OUT dbid oid,
	OUT queryid bigint,
	OUT query text,
	OUT calls bigint,
	OUT total_time double precision,
	OUT min_time double precision,
	OUT max_time double precision,
	OUT mean_time double precision,
	OUT stddev_time double precision,
	OUT rows bigint,
	OUT shared_blks_hit bigint,
	OUT shared_blks_read bigint,
	OUT shared_blks_dirtied bigint,
	OUT shared_blks_written bigint,
	OUT local_blks_hit bigint,
	OUT local_blks_read bigint,
	OUT local_blks_dirtied bigint,
	OUT local_blks_written bigint,
	OUT temp_blks_read bigint,
	OUT temp_blks_written bigint,
	OUT blk_read_time double precision,
	OUT blk_write_time double precision)
    RETURNS SETOF record 
    LANGUAGE 'c'
    COST 1
    VOLATILE STRICT 
    ROWS 1000
AS '$libdir/pg_stat_statements', 'pg_stat_statements_1_3'
;

ALTER FUNCTION public.pg_stat_statements(boolean)
    OWNER TO azure_superuser;

5. Function pg_stat_statements_reset

---

CREATE OR REPLACE FUNCTION public.pg_stat_statements_reset(
	)
    RETURNS void
    LANGUAGE 'c'
    COST 1
    VOLATILE 
AS '$libdir/pg_stat_statements', 'pg_stat_statements_reset'
;

ALTER FUNCTION public.pg_stat_statements_reset()
    OWNER TO azure_superuser;

GRANT EXECUTE ON FUNCTION public.pg_stat_statements_reset() TO azure_superuser;

REVOKE ALL ON FUNCTION public.pg_stat_statements_reset() FROM PUBLIC;

Execute the following SQL-scripts to set up Views

1. View pg_buffercache

---

CREATE OR REPLACE VIEW public.pg_buffercache
 AS
 SELECT p.bufferid,
    p.relfilenode,
    p.reltablespace,
    p.reldatabase,
    p.relforknumber,
    p.relblocknumber,
    p.isdirty,
    p.usagecount,
    p.pinning_backends
   FROM pg_buffercache_pages() p(bufferid integer, relfilenode oid, reltablespace oid, reldatabase oid, relforknumber smallint, relblocknumber bigint, isdirty boolean, usagecount smallint, pinning_backends integer);

ALTER TABLE public.pg_buffercache
    OWNER TO azure_superuser;

GRANT ALL ON TABLE public.pg_buffercache TO azure_superuser;
GRANT SELECT ON TABLE public.pg_buffercache TO azure_pg_admin;

2. View pg_stat_statements

---

CREATE OR REPLACE VIEW public.pg_stat_statements
 AS
 SELECT pg_stat_statements.userid,
    pg_stat_statements.dbid,
    pg_stat_statements.queryid,
    pg_stat_statements.query,
    pg_stat_statements.calls,
    pg_stat_statements.total_time,
    pg_stat_statements.min_time,
    pg_stat_statements.max_time,
    pg_stat_statements.mean_time,
    pg_stat_statements.stddev_time,
    pg_stat_statements.rows,
    pg_stat_statements.shared_blks_hit,
    pg_stat_statements.shared_blks_read,
    pg_stat_statements.shared_blks_dirtied,
    pg_stat_statements.shared_blks_written,
    pg_stat_statements.local_blks_hit,
    pg_stat_statements.local_blks_read,
    pg_stat_statements.local_blks_dirtied,
    pg_stat_statements.local_blks_written,
    pg_stat_statements.temp_blks_read,
    pg_stat_statements.temp_blks_written,
    pg_stat_statements.blk_read_time,
    pg_stat_statements.blk_write_time
   FROM pg_stat_statements(true) pg_stat_statements(userid, dbid, queryid, query, calls, total_time, min_time, max_time, mean_time, stddev_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time);

ALTER TABLE public.pg_stat_statements
    OWNER TO azure_superuser;

GRANT ALL ON TABLE public.pg_stat_statements TO azure_superuser;
GRANT SELECT ON TABLE public.pg_stat_statements TO PUBLIC;