Queries

SELECT 
					partition_schema, partition_table, suffix_timestamp, table_exists 
				FROM partman.show_partition_name($1, $2)

SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), $1, $2) AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), $3, $4) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname IS NOT NULL ORDER BY 1, 2 /*pghero*/

WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "total_minutes" DESC LIMIT $6 /*pghero*/

SELECT pg_advisory_lock($1)

SELECT id, device_id, target_id, target_type, 
	mapped_at, unmapped_at, mapped_recorded_at, unmapped_recorded_at 
	FROM device_mappings 
	WHERE device_id = $1 AND
	((unmapped_at IS NULL AND mapped_at <= $2) OR
	 (unmapped_at IS NOT NULL AND mapped_at <= $2 AND unmapped_at > $2))

SELECT n.nspname AS table_schema, c.relname AS table, attname AS column, format_type(a.atttypid, a.atttypmod) AS column_type, pg_get_expr(d.adbin, d.adrelid) AS default_value FROM pg_catalog.pg_attribute a INNER JOIN pg_catalog.pg_class c ON c.oid = a.attrelid INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum) WHERE NOT a.attisdropped AND a.attnum > $1 AND pg_get_expr(d.adbin, d.adrelid) LIKE $2 AND n.nspname NOT LIKE $3 /*pghero*/

SELECT COUNT($2) FROM information_schema.tables WHERE table_name = $1 AND table_schema = (SELECT current_schema()) LIMIT $3

SELECT n.nspname AS schema, c.relname AS table, $1 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid)) AS transactions_left FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind = $2 AND ($3 - GREATEST(AGE(c.relfrozenxid), AGE(t.relfrozenxid))) < $4 ORDER BY 3, 1, 2 /*pghero*/

SELECT version, dirty FROM "schema_migrations" LIMIT $1

SELECT CURRENT_DATABASE()

SELECT pg_advisory_unlock($1)

SELECT n.nspname AS schema, c.relname AS sequence, has_sequence_privilege(c.oid, $1) AS readable FROM pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = $2 AND n.nspname NOT IN ($3, $4) /*pghero*/

SELECT pid, state, application_name AS source, age(NOW(), COALESCE(query_start, xact_start)) AS duration, (wait_event IS NOT NULL) AS waiting, query, COALESCE(query_start, xact_start) AS started_at, EXTRACT($1 FROM NOW() - COALESCE(query_start, xact_start)) * $2 AS duration_ms, usename AS user, backend_type FROM pg_stat_activity WHERE state <> $3 AND pid <> pg_backend_pid() AND datname = current_database() AND NOW() - COALESCE(query_start, xact_start) > interval $4 AND query <> $5 ORDER BY COALESCE(query_start, xact_start) DESC /*pghero*/

SELECT state, COUNT(*) AS connections FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC, 1 /*pghero*/

INSERT INTO device_changes(device_id, certificate_thumbprint, 
		public_key_thumbprint, serial_number, device_model, manufacturer_name) VALUES ($1, $2, $3, $4, $5, $6) ON CONFLICT(device_id, certificate_thumbprint) DO NOTHING

SELECT nsp.nspname AS schema, rel.relname AS table, con.conname AS name, fnsp.nspname AS referenced_schema, frel.relname AS referenced_table FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid LEFT JOIN pg_catalog.pg_class frel ON frel.oid = con.confrelid LEFT JOIN pg_catalog.pg_namespace nsp ON nsp.oid = con.connamespace LEFT JOIN pg_catalog.pg_namespace fnsp ON fnsp.oid = frel.relnamespace WHERE con.convalidated = $1 /*pghero*/

INSERT INTO cpls (
				id, is_smpte, content_title_text, 
				annotation_text, original_content_type, content_type, 
				creator, rating_ids, duration
			) 
			VALUES($1, $2, $3, 
				$4, $5, $6, 
				$7, $8, $9
			) ON CONFLICT (id) 
			DO UPDATE SET
				is_smpte = EXCLUDED.is_smpte,
				content_title_text = EXCLUDED.content_title_text,
				annotation_text = EXCLUDED.annotation_text,
				original_content_type = COALESCE(cpls.original_content_type, EXCLUDED.original_content_type),
				content_type = COALESCE(cpls.content_type, EXCLUDED.content_type),
				creator = EXCLUDED.creator,
				rating_ids = EXCLUDED.rating_ids,
				duration = GREATEST(COALESCE(cpls.duration, $10), COALESCE(EXCLUDED.duration, $11))

SELECT slot_name, database, active FROM pg_replication_slots /*pghero*/

begin

INSERT INTO device_mappings(id, device_id, target_id, target_type, mapped_at, 
	unmapped_at, mapped_recorded_at, unmapped_recorded_at) VALUES($1, $2, $3, $4, $5, $6, $7, $8)

SELECT EXISTS (SELECT $2 FROM cities WHERE id=$1)

UPDATE device_mappings SET unmapped_at = $1, unmapped_recorded_at = $2, updated_at = NOW()
	WHERE device_id = $3 AND target_id = $4 AND target_type = $5 AND unmapped_at IS NULL

rollback

SELECT EXISTS(
	SELECT 1 
	FROM device_mappings 
	WHERE device_id = $1 AND target_id = $2 AND target_type = $3 AND mapped_at = $4)

INSERT INTO movies (id, qube_erp_ids, eidr_ids, third_party_ids, name, language_id, release_dates, certifications) 
			VALUES($1, $2, $3, $4, $5, $6, $7, $8) 
			ON CONFLICT (id) DO UPDATE SET 
				qube_erp_ids = excluded.qube_erp_ids, 
				eidr_ids = excluded.eidr_ids, 
				third_party_ids = excluded.third_party_ids, 
				name = excluded.name, 
				language_id = excluded.language_id, 
				release_dates = excluded.release_dates, 
				certifications = excluded.certifications,
				updated_at = (now() at time zone $9)

INSERT INTO languages (id, name) VALUES($1, $2)
		ON CONFLICT (id) DO UPDATE SET name = excluded.name, updated_at = (now() at time zone $3)

INSERT INTO movie_cpl_mappings (
					movie_id, cpl_id, part_number
			)
			VALUES ($1::uuid, $2::uuid, $3::smallint)
			ON CONFLICT (cpl_id)
			DO UPDATE SET
					movie_id = excluded.movie_id, 
					part_number = COALESCE(excluded.part_number, movie_cpl_mappings.part_number),
					updated_at = (now() at time zone $4)

SELECT
			mcm.movie_id, mcm.cpl_id, c.content_title_text
		FROM 
			movie_cpl_mappings mcm
		INNER JOIN 
			cpls c ON mcm.cpl_id = c.id AND c.content_type = $1
		WHERE
			mcm.part_number IS NULL AND
			mcm.updated_at >= CURRENT_DATE - INTERVAL $2

SELECT 
			c.id, 
			c.content_title_text, 
			c.creator,
			c.content_type
		FROM 
			cpls c
		WHERE 
			c.content_type = $1 AND
			c.created_at >= CURRENT_DATE - INTERVAL $3 AND
			(c.duration IS NULL OR c.duration = $4 OR c.duration >= $2) AND
			NOT EXISTS (
				SELECT $5 
				FROM movie_cpl_mappings mcm 
				WHERE mcm.cpl_id = c.id
			)

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typname IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40)

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typelem IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49)

SELECT 
			c.id, 
			c.content_title_text, 
			c.duration,
			c.content_type
		FROM 
			cpls c
		WHERE 
			c.created_at >= CURRENT_DATE - INTERVAL $1 AND
			(c.content_type IS NULL OR c.duration IS NULL OR c.duration = $2)

SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
FROM pg_type as t
LEFT JOIN pg_range as r ON oid = rngtypid
WHERE
  t.typtype IN ($1, $2, $3)

SELECT t.oid, t.typname
FROM pg_type as t
WHERE t.typname IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)

SET client_min_messages TO 'warning'

SET SESSION timezone TO 'UTC'

SELECT id, is_smpte, content_title_text, annotation_text, original_content_type, 
	content_type, creator, rating_ids::TEXT[], duration, created_at FROM cpls WHERE id = ANY($1)

SET standard_conforming_strings = on

SET intervalstyle = iso_8601

SELECT EXISTS(SELECT $2 FROM movies WHERE id=$1)