Adatbáziskezelés (TTBML1440)

Index

CREATE INDEX trgm_idx_faj ON onpi USING gin (faj gin_trgm_ops);

JOIN

LEFT JOIN

SELECT oduszam,odu,kihelyezes,megszunes, c.megjegyzes, datum, ce.megjegyzes
FROM coracias c LEFT JOIN coracias_ellenorzesek ce ON odu=oduszam
WHERE megszunes IS NOT NULL AND elo_nemelo='élő'
ORDER BY oduszam, datum ASC

Natural JOIN

trgm search

SET pg_trgm.similarity_threshold = 0.7;

SELECT obm_id,content,species,species_name,similarity(species,species_name) FROM public_nestbox_data_observations
LEFT JOIN shared.superspecies ON (species % species_name)
WHERE species_name IS NOT NULL AND content not ilike 'empty' AND content not ilike 'other' AND content not ilike 'üres%'

SET pg_trgm.similarity_threshold = 0.6;

SELECT obm_id,content,species,species_name,similarity(species,species_name) as s
FROM public_nestbox_data_observations
LEFT JOIN shared.superspecies ON (species % species_name)
WHERE
 similarity(species,species_name) < 1

DATE_PART

SELECT DATE_PART('day', megszunes::timestamp - kihelyezes::timestamp) AS days FROM "coracias" WHERE megszunes IS NOT NULL AND kihelyezes IS NOT NULL ORDER BY "days"

TRIGGER

-- Name: file_connect_status_update(); Type: FUNCTION; Schema: public; Owner: dinpi_admin
--
CREATE FUNCTION public.file_connect_status_update() RETURNS trigger
LANGUAGE plpgsql
AS BEGIN\$\$

IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
     IF (new.obm_files_id IS NOT NULL) THEN
         UPDATE system.file_connect SET temporal=false WHERE temporal=true AND conid=new.obm_files_id;
     END IF;
END IF;

RETURN new;
END \$\$