Úvod do PL/pgSQL - SQL Procedural Language

CREATE OR REPLACE FUNCTION greet()
	RETURNS text
	LANGUAGE plpgsql
AS $function$
	BEGIN
		RETURN 'hello world!';
	END;
$function$
;

CREATE OR REPLACE FUNCTION greet_name(name text)
	RETURNS text
	LANGUAGE plpgsql
AS $function$
	begin
		return 'hello ' || name || '!';
	END;
$function$
;

select *, greet_name(cit_name) from c_city cc;

CREATE OR REPLACE FUNCTION movies.fn_c_city_ins(cit_name text, xcit_cou_id text, out cit_id integer)
	RETURNS int4
	LANGUAGE plpgsql
AS $function$
DECLARE
	BEGIN
		WITH vlozene AS (
			INSERT INTO c_city(
				cit_name, xcit_cou_id
				)
			VALUES (
				fn_c_city_ins.cit_name, fn_c_city_ins.xcit_cou_id
				)
				RETURNING
					c_city.cit_id
		)
		SELECT
			vlozene.cit_id
		INTO
			fn_c_city_ins.cit_id
		FROM
			vlozene
		;
		RETURN;
	END;
$function$
;

CREATE OR REPLACE PROCEDURE movies.pr_c_city_upd(cit_id integer, cit_name text, xcit_cou_id text, cit_modified_by text DEFAULT NULL::text)
 LANGUAGE plpgsql
AS $procedure$
	begin
		update c_city
		set cit_name = pr_c_city_upd.cit_name, 
			xcit_cou_id = pr_c_city_upd.xcit_cou_id, 
			cit_modified_by = coalesce(pr_c_city_upd.cit_modified_by, "session_user"()::text), 
			cit_modified = now()
		where c_city.cit_id = pr_c_city_upd.cit_id
		;
    
	END;
$procedure$
;

CREATE OR REPLACE PROCEDURE movies.pr_c_city_del(cit_id integer)
	LANGUAGE plpgsql
AS $procedure$
	begin
		delete from c_city 
		where c_city.cit_id = pr_c_city_del.cit_id ;
	END;
$procedure$
;

CREATE OR REPLACE FUNCTION movies.fn_c_city_sel(podmienka text)
 RETURNS TABLE(cit_id integer, cit_name character varying, cit_inserted_by text, cit_inserted timestamp without time zone, cit_modified_by text, cit_modified timestamp without time zone, xcit_cou_id char)
 LANGUAGE plpgsql
AS $function$
	begin
		RETURN QUERY
		execute
		'SELECT
				cit_id
			    , cit_name
				, cit_inserted_by
				, cit_inserted
				, cit_modified_by
				, cit_modified
				, xcit_cou_id
		FROM
			c_city '::text
			|| coalesce(' WHERE ' || fn_c_city_sel.podmienka , ''::text)
			|| ' order by cit_name asc'::text
		;
	END;
$function$
;

CREATE OR REPLACE FUNCTION movies.fn_den(cislo_dna integer)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
dni varchar[]:= array ['Pondelok','Utorok','Streda','Štvrtok','Piatok','Sobota','Nedeľa'];
i int;
vystup varchar;
	BEGIN
		if (cislo_dna > 0 and cislo_dna < 8) then 
			for i in 1..7 loop 
				if i = cislo_dna then
					vystup:=dni[i];
				end if;
			--raise notice 'den: %', dni[i];
			end loop;
		else
			vystup:='Cislo mimo rozsah';
		end if;
	return vystup;
    
	END;
$function$
;

-- DROP TABLE t_log;
  
CREATE TABLE t_log (
	log_id serial NOT NULL,
	log_event text NOT NULL,
	log_time timestamp(0) NOT NULL DEFAULT now(),
	log_user text NOT NULL DEFAULT SESSION_USER,
	log_object text NOT NULL,
	log_value float8 NOT NULL,
	CONSTRAINT t_log_pk PRIMARY KEY (log_id)
);

CREATE OR REPLACE FUNCTION movies.fn_t_log_ins(log_event text, log_object text, log_value real, OUT log_id integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
	BEGIN
		WITH vlozene AS (
			INSERT INTO t_log (log_event, log_object , log_value )
			VALUES ( fn_t_log_ins.log_event, fn_t_log_ins.log_object, fn_t_log_ins.log_value)
			RETURNING
					t_log.log_id
		)
		SELECT
			vlozene.log_id
		INTO
			fn_t_log_ins.log_id
		FROM
			vlozene
		;
	
		RETURN;
  
	END;
$function$
;

CREATE OR REPLACE FUNCTION movies.greet()
 RETURNS text
 LANGUAGE plpgsql
AS $function$
declare 
t timestamptz := clock_timestamp();
	begin
		perform pg_sleep(random());
		perform fn_t_log_ins('EXECUTE','greet()',extract(second from clock_timestamp() - t)::real);	 --perform ked nechceme spracovat vystup z volania
		RETURN 'hello world!';
	END;
$function$
;

CREATE OR REPLACE FUNCTION movies.fn_kontrola_psc_sk(psc text)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare 
	dlzka int; 
	i int;
	begin
		dlzka:=char_length(fn_kontrola_psc_sk.psc);	
		if dlzka<>5 then
			raise notice 'Nesedi dlzka zadaneho PSC, musi byt 5';
			return 1;
		end if;
		
		for i in 1..5 loop
			if ascii(substring(fn_kontrola_psc_sk.psc from i for 1)) < 48 or ascii(substring(fn_kontrola_psc_sk.psc from i for 1)) > 57 then
				raise notice 'Na pozicii % nie je cislica',i;
				return 1;
			end if;
		end loop;
	
	return 0;   --0 - ok, 1 - nesedi PSC, 2 - chyba funkcie
	END;
$function$
;

CREATE OR REPLACE FUNCTION movies.fn_psc_cinema()
	RETURNS int4
	LANGUAGE plpgsql
AS $function$
declare 
--zaznam movies.t_cinema%ROWTYPE;
cur_cinema CURSOR FOR SELECT * FROM t_cinema;
riadok record;
psc_spravne integer;
	begin
		open cur_cinema;
loop
	fetch cur_cinema into riadok;
	exit when not found;
   
	select fn_kontrola_psc_sk(riadok.cin_postal_code) into psc_spravne;
	if (psc_spravne<>0) then
	raise notice 'Riadok s ID: % %' ,riadok.cin_id, riadok.cin_name;
return 1;
	end if;
end loop;	
  
close cur_cinema;	
return 0;
  
END;
$function$
;