ឯកសារស្រាវជ្រាវខ្លីៗអំពី SQL ភាគ១

៙ របៀបបង្កើត ភាសាដើម្បីដំណើរការ Function
[postgresql language plpgsql does not exist]

+ PostgreSQL:

CREATE LANGUAGE plpgsql;

៙ របៀបបង្កើត ID random មិនជាន់គ្នា
[make unique random id in postgresql]

+ PostgreSQL:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

យើងអាចប្រើវាបានដោយហៅ uuid_generate_v1() ហើយ field របស់យើងត្រូវមាន datatype ជា uuid

៙ របៀបបង្កើត Function ដែល insert ហើយ return record អំបាញ់មិញ
[insert into 2 differ table and return current record]

+ PostgreSQL:

CREATE OR REPLACE FUNCTION insert2tables(tcher_name VARCHAR,stu_name VARCHAR stu_program VARCHAR)
RETURNS void AS $$
DECLARE
last_id uuid;
BEGIN
--Return with data column[tcher_id] back by generate id by uuid_gernerate_v1()
INSERT INTO tb_teacher VALUES(uuid_generate_v1(),$1) RETURNING tcher_id INTO last_id;
--get that id then insert to other table
INSERT INTO tb_program VALUES(uuid_generate_v1(),$2,$3,CAST(last_id AS UUID));
END;
$$ LANGUAGE 'plpgsql'

+ Source:ទីនេះ

៙ របៀបប្តូរ row ពី column
[how to convert row to columns in postgresql]

+ PostgreSQL:
ឧទា៖ ខ្ញុំមាន table ឈ្មោះ tb_fee_types ដែលមានទិន្នន័យ

ខ្ញុំចង់ប្តូរ row [TERM,SEM,YEAR] ទៅជា column

SELECT 
DISTINCT(f.fee_type_id), 
tm.price_fee AS term,
y.price_fee AS "year",
sem.price_fee AS semester
FROM tb_fee_types AS f,
(SELECT * FROM tb_fee_types WHERE type_fee='TERM') AS tm,
(SELECT * FROM tb_fee_types WHERE type_fee='YEAR') AS y,
(SELECT * FROM tb_fee_types WHERE type_fee='SEMESTER') AS sem ORDER BY fee_type_id ASC;

+ Source:ទីនេះ