---EXAMPLE 1=========================================
CREATE FUNCTION clean_old_invitations()
RETURNS void AS
$$
DELETE FROM invitation
WHERE "theStatus" = 'PENDING'
AND "dateSent" < CURRENT_DATE - INTERVAL '2 years 10 months';
$$
LANGUAGE SQL;
------------------
DATE,TIME Tutorial
------------------
select DATE '2026-03-08' as dt;
select TIME '14:30';
select TIMESTAMP '2026-03-08 14:30:00';
select INTERVAL '2 months';

CURRENT_DATE		today’s date
CURRENT_TIME		current time
CURRENT_TIMESTAMP	current date and time
NOW()			same as current timestamp

---date arithmetic
SELECT CURRENT_DATE + INTERVAL '10 days';
SELECT CURRENT_DATE - INTERVAL '2 months';
---difference
SELECT DATE '2022-04-08' - DATE '2026-03-01'; // Result in days
---age
SELECT AGE(CURRENT_DATE, DATE '2000-05-10');  // Result interval
---Extracting Parts of Dates
SELECT EXTRACT(YEAR FROM CURRENT_DATE);
SELECT EXTRACT(MONTH FROM CURRENT_DATE);
SELECT EXTRACT(DOW FROM CURRENT_DATE);
---Converting Dates to Text
SELECT TO_CHAR(CURRENT_DATE, 'DD/MM/YYYY');
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI');
--- Comparing Dates
SELECT *
FROM invitation
WHERE dateSent < CURRENT_DATE - INTERVAL '2 years';

---EXAMPLE 2=========================================

CREATE FUNCTION get_member_country(p_email varchar)
RETURNS varchar AS
$$
SELECT country
FROM member
WHERE email = p_email; --- or $1
$$
LANGUAGE SQL;

---EXAMPLE 3=========================================
CREATE FUNCTION connection_count(p_email varchar)
RETURNS integer AS
$$
SELECT COUNT(*)
FROM connects
WHERE "email" = p_email;
$$
LANGUAGE SQL;

SELECT connection_count('ae517b0520@example.com');

---Use inside other queries
SELECT email, connection_count(email), get_member_country(email)
FROM member;

---EXAMPLE 4=========================================
---Function receiving a composite type

CREATE FUNCTION full_name(m member)
RETURNS text
LANGUAGE SQL
AS $$
SELECT m."firstName" || ' ' || m."secondName";
$$;

select full_name(m.*) from member m

---EXAMPLE 5=========================================
---Function Returning a composite type
CREATE FUNCTION get_member(p_email varchar)
RETURNS member
LANGUAGE SQL
AS $$
SELECT *
FROM member
WHERE email = p_email;
$$;

SELECT get_member('ae517b0520@example.com');

---Use of ROW
---Find accepted invitations that already produced connections.
SELECT *
FROM invitation i
JOIN connects c
ON i."senderEmail" = c.email
AND i."receiverEmail" = c."connectedWithEmail";

---Using ROW:

SELECT *
FROM invitation i
JOIN connects c
ON ROW(i."senderEmail", i."receiverEmail")
 = ROW(c.email, c."connectedWithEmail");

---EXAMPLE 6=========================================

CREATE FUNCTION count_members_by_countries(VARIADIC countries text[])
RETURNS integer
LANGUAGE SQL
AS $$
SELECT COUNT(*)
FROM member
WHERE country = ANY(countries);
$$;

SELECT count_members_by_countries('Greece','Italy','Portugal');

---EXAMPLE 7=========================================

---Function returning members from a country
CREATE FUNCTION members_from_country(p_country text)
RETURNS SETOF member
LANGUAGE SQL
AS $$
SELECT *
FROM member
WHERE country = p_country;
$$;
--Use as table source
SELECT *
FROM members_from_country('Greece');

--Selecting specific columns
SELECT email, firstName, secondName
FROM members_from_country('Greece');
--Joining a function result with a table
--Example: find connections of Greek members.

SELECT m.email, c.email2
FROM members_from_country('Greece') m
JOIN connects c
ON m.email = c.email1;

--RETURN SETS
--a)
CREATE FUNCTION members_from_country(p_country text)
RETURNS SETOF member
LANGUAGE SQL
AS $$
SELECT *
FROM member
WHERE country = p_country;
$$;
b)
CREATE FUNCTION members_from_country(p_country text)
RETURNS TABLE(
    email text,
    firstName text,
    secondName text,
    country text
)
LANGUAGE SQL
AS $$
SELECT email, firstName, secondName, country
FROM member
WHERE country = p_country;
$$; 
c)
CREATE FUNCTION members_from_country(p_country text)
RETURNS SETOF record
LANGUAGE SQL
AS $$
SELECT email, "firstName", "secondName"
FROM member
WHERE country = p_country;
$$;

--Usage requires defining the columns:
SELECT *
FROM members_from_country('Greece')
AS m(email text, firstName text, secondName text);

d)
CREATE FUNCTION members_from_country(
    p_country text,
    OUT email text,
    OUT firstName text,
    OUT secondName text
)
RETURNS SETOF record
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT email, firstName, secondName
    FROM member
    WHERE country = p_country;
END;
$$;