Queries

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 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 pg_advisory_lock($1)

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 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 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 qwc_device_id, last_played_time FROM device_last_played_time WHERE qwc_device_id = ANY($1)

SELECT version, dirty FROM "schema_migrations" LIMIT $1

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

SELECT CURRENT_DATABASE()

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*/

SELECT pg_advisory_unlock($1)

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

SELECT id, name, 
			iso639_1, iso639_2, iana, 
			created_at, updated_at FROM languages 
		WHERE 
			LOWER(iso639_1) = LOWER($1) OR 
			LOWER(iso639_2) = LOWER($1) OR 
			LOWER(iana) = LOWER($1) OR
			LOWER(name) = LOWER($1)
		ORDER BY
			CASE 
				WHEN LOWER(iso639_1) = LOWER($1) THEN $2
				WHEN LOWER(iso639_2) = LOWER($1) THEN $3
				WHEN LOWER(iana) = LOWER($1) THEN $4
				WHEN LOWER(name) = LOWER($1) THEN $5
				ELSE $6
			END
		LIMIT $7

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

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 = EXCLUDED.original_content_type,
				content_type = EXCLUDED.content_type,
				creator = EXCLUDED.creator,
				rating_ids = EXCLUDED.rating_ids,
				duration = EXCLUDED.duration

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)

INSERT INTO chains (
			id, name, display_name, 
			address, area, postal_code, city_id
		) 
		VALUES (
			$1, $2, $3, 
			$4, $5, $6, $7
		) 
		ON CONFLICT (id) DO UPDATE SET 
			name = excluded.name, 
			display_name = excluded.display_name, 
			address = excluded.address, 
			area = excluded.area, 
			postal_code = excluded.postal_code, 
			city_id = excluded.city_id, 
			updated_at = timezone($8::text, now())

begin

INSERT INTO theatres (
			id, name, address, 
			chain_id, city_id, is_deleted, 
			is_listed, is_verified, is_private, 
			identifiers, integrators, thirdparty_domain_identifiers, 
			timezone, facility_location_type, facility_type_data, 
			geolocation, agentqlog_running_on, radar_agent_running_on, content_agent_running_on
		) 
		VALUES(
			$1, $2, $3, 
			$4, $5, $6, 
			$7, $8, $9, 
			$10, $11, $12, 
			$13, $14, $15,
			$16, $17, $18, $19
		) 
		ON CONFLICT (id) DO UPDATE SET 
			name = excluded.name, 
			address = excluded.address, 
			chain_id = excluded.chain_id, 
			city_id = excluded.city_id, 
			is_deleted = excluded.is_deleted, 
			is_listed = excluded.is_listed, 
			is_verified = excluded.is_verified,
			is_private = excluded.is_private,
			identifiers = excluded.identifiers,
			integrators = excluded.integrators,
			thirdparty_domain_identifiers = excluded.thirdparty_domain_identifiers,
			timezone = excluded.timezone,
			facility_location_type = excluded.facility_location_type,
			facility_type_data = excluded.facility_type_data,
			geolocation = excluded.geolocation,
			agentqlog_running_on = COALESCE(excluded.agentqlog_running_on, theatres.agentqlog_running_on),
			radar_agent_running_on = COALESCE(excluded.radar_agent_running_on, theatres.radar_agent_running_on),
			content_agent_running_on = COALESCE(excluded.content_agent_running_on, theatres.content_agent_running_on),
			updated_at = timezone($20::text, now())

INSERT INTO public.dolby_sms_log_events_p20260701 (
				event_id, event_time, device_identifier, device_identifier_type,
				event_class, event_type, event_sub_type,
				parameters, exceptions, reference_ids,
				event_sequence, event_time_sequence
			)
			VALUES ($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, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60), ($61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72) 
			ON CONFLICT (event_id, device_identifier, device_identifier_type)
			DO UPDATE SET
					event_sequence = EXCLUDED.event_sequence

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
			event_id, event_time, device_identifier, 
			device_identifier_type, event_sub_type, content_id, kdm_id,
			first_frame, last_frame, parameters, exceptions, playback_report_processed, created_at, 
			event_time_sequence, playback_report_id, event_sequence
		FROM
			dolby_sms_log_playout_events 
		WHERE
			device_identifier=$1 AND 
			event_time_sequence >= $2 AND
			event_time_sequence <= $3 AND
			playback_report_processed = $5 AND
			event_sub_type = ANY($4)
		ORDER BY
			event_time_sequence ASC

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, $50)

SELECT
						DATE_TRUNC($2, MAX(event_time_sequence))
					FROM
						dolby_sms_log_playout_events
					WHERE
						device_identifier = $1

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)

SELECT
						DATE_TRUNC($2, MAX(event_time_sequence))
					FROM
						dolby_sms_log_events
					WHERE
						device_identifier = $1

SET client_min_messages TO 'warning'

INSERT INTO device_last_played_time (qwc_device_id, last_played_time) 
		VALUES ($1, jsonb_build_object($4, $2::TEXT)) 
		ON CONFLICT (qwc_device_id) 
		DO UPDATE 
		SET 
			last_played_time = jsonb_set(
				device_last_played_time.last_played_time, 
				$5, 
				to_jsonb($2::TEXT) 
			), 
			updated_at = $3

SET SESSION timezone TO 'UTC'

SET standard_conforming_strings = on

SET intervalstyle = iso_8601