การสร้าง Function ใน PostgreSQL
กระทู้เก่าบอร์ด อ.สุภาพ ไชยา

 1,313   8
URL.หัวข้อ / URL
การสร้าง Function ใน PostgreSQL

สมมติต้องการให้ฟีลด์ product_id มีรูปแบบ

PROD000001
PROD000004
PROD000003


ไปเรื่อยๆ

ถ้าเป็นใน Access ก็ทำได้โดยการเขียนโค้ดในฟอร์มช่วย จากตัวอย่างเก่าๆ ผมจะใช้ Double Click event ของ Text Box ของฟีลด์เป้าหมายช่วยใส่รหัสนี้ให้

แต่ถ้าเป็นใน PostgreSQL เราสามารถเขียน Function ไว้ในฐานข้อมูลช่วยได้

ความสามารถตรงนี้ยังไม่มีใน Access ครับ

ถ้าเป็น Postgres ผมจะนึกถึงฟังก์ชันขึ้นมาทันที่ เพื่อจะนำมาช่วยงานครั้งนี้

1. สร้างตารางเป้าหมายก่อน

ให้มี 2 ฟีลด์คือ product_id และ product_name

mydb=# create table products (
mydb(# product_id char(10),
mydb(# product_name varchar(255)
mydb(# );
CREATE

เปิดดูโครงการสร้างของตารางที่เพิ่งจะสร้างขึ้น

mydb=# \d products
Table "products"
Column | Type | Modifiers
--------------+-----------------------+-----------
product_id | character(10) |
product_name | character varying(80) |

2. ใช้ count(*) ช่วยในการนับจำนวนข้อมูลว่ามีกี่อันในตารางเป้าหมาย

ต้องสร้างเป็นฟังก์ชันช่วย ผมตั้งชื่อฟังก์ชัน mymax() โดยคืนค่าเป็น bigint

mydb=# create or replace function mymax() returns bigint as '
mydb'# select count(*) from products;
mydb'# ' language sql;
CREATE

ลองทดสอบ

mydb=# select mymax();
mymax
-------
0
(1 row)

3. สร้างอีกฟังก์ชันหนึ่ง เพื่อจัดรูปแบบของ product_id ให้เป็นตามต้องการคือ PRODXXXXXX

ผมจะใช้ฟังก์ชัน rpad(), ltrim() ของ Postgres ช่วย

(ดูฟังก์ชันที่จัดการ String ของ Postgres ได้ที่หน้า http://agserver.kku.ac.th/agro/html/functions-string.html)

mydb=# create or replace function mynext() returns text as '
mydb'# select rpad(''PROD'',10,ltrim(to_char(mymax()+1, ''000009''),'' ''));
mydb'# ' language SQL;
CREATE

ฟังก์ชันข้างบนชื่อ mynext() คืนค่ามาเป็น text

ทดสอบ

mydb=# select mynext();
mynext
------------
PROD000001
(1 row)

4. ลองเพิ่มข้อมูล ใช้ insert into ชื่อตารางเป้าหมาย ค่าที่จะใส่ลงไป

mydb=# insert into products values (
mydb'# mynext(), ‘Postgres’);

เพื่อให้การเพิ่มข้อมูลของเราทำได้เร็วยิ่งขึ้น ลองเขียนฟังก์ชันสำหรับเพิ่มข้อมูลขึ้นมาอีกอัน

mydb=# create or replace function myadd(text) returns integer as '
mydb'# insert into products values (
mydb'# mynext(), $1);
mydb'# select 1;
mydb'# ' language sql;
CREATE

ฟังก์ชันนี้ชื่อ myadd() รับค่าเป็น text แล้วคืนค่าเป็น integer

ลองเพิ่มข้อมูลโดยใช้ฟังก์ชันข้างต้น

mydb=# select myadd('Oracle 8i');
myadd
-------
1
(1 row)

ลองวิวดูข้อมูลที่เพิ่งใส่ลงไป

mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
PROD000002 | Oracle 8i
(2 rows)

จะเห็นว่ามี product_id ลำดับถัดไปให้เองอัตโนมัติ

5. ลองใช้ default value ของฟีลด์เป้าหมายช่วยในการเพิ่มข้อมูลก็ได้

ผมใช้ default mynext() เป็นค่า Default Value ของฟีลด์ชื่อ product_id

ลบตารางเก่าทิ้งก่อน

mydb=# drop table products;

แล้วสร้างใหม่

mydb=# create table products (
mydb(# product_id char(10) default mynext(),
mydb(# product_name varchar(255)
mydb(# );
CREATE

ดูโครงสร้างของตารางที่สร้างขึ้น

mydb=# \d products;
Table "products"
Column | Type | Modifiers
--------------+------------------------+------------------
product_id | character(10) | default mynext()
product_name | character varying(255) |

ให้สังเกตตางช่อง Modifiers ของฟีลด์แรกครับ จะมี default mynext() ด้วย

ลองใส่ข้อมูลแบบใช้ insert into

mydb=# insert into products (product_name) values('PostgreSQL');
INSERT 237802 1

จะสังเกตเห็นว่า ผมจะบอกแค่ฟีลด์ product_name อย่างเดียว เพราะฟีลด์ product_id จะมีการเพิ่มรหัสถัดไปให้เราเอง

ดูผลลัพธ์

mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
(1 row)

ลองใส่ข้อมูลอีกที

mydb=# insert into products (product_name) values('MySQL');
INSERT 237803 1

ดูผลลัพธ์
mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
PROD000002 | MySQL
(2 rows)

ลองสร้างตารางเดิม แต่กำหนดให้ product_id เป็น Primary Key ด้วย
อย่าลืมลบตารางเก่าทิ้งก่อน

mydb=# create table products (
mydb(# product_id char(10) default mynext(),
mydb(# product_name varchar(255)
mydb(# ,
mydb(# primary key(product_id)
mydb(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'products_pkey' for table 'products'
CREATE

ผมเอา Primary Key มาต่อท้ายสุด ซึ่งจะสามารถระบุได้หลายๆ ฟีลด์

หรือจะเอา Primary Key มาต่อท้ายชื่อฟีลด์ก็ได้

mydb=# create table products (
product_id char(10) primary key default mynext(),
product_name varchar(255)
);

ลองดูตารางที่สร้างขึ้น

mydb=# \d products
Table "products"
Column | Type | Modifiers
--------------+------------------------+---------------------------
product_id | character(10) | not null default mynext()
product_name | character varying(255) |
Primary key: products_pkey

ลองเพิ่มข้อมูลโดยเรียกฟังก์ชัน myadd()

mydb=# select myadd('PostgreSQL');
myadd
-------
1
(1 row)


mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
(1 row)

หรือใส่ข้อมูลในรูปแบบ insert into

mydb=# insert into products(product_name) values('MySQL');
INSERT 237810 1

จะสังเกตเห็นว่า เมื่อกำหนดค่า default ของฟีลด์ product_id แล้ว ไม่ต้องระบุสิ่งที่จะใส่ลงไปในฟีลด์นี้อีก

และถ้าเรา Link ตารางนี้ไปยัง Access เราก็แค่กรอกชื่อ product_name ลงไปอย่างเดียวเช่นกัน

mydb=# select * from products;
product_id | product_name
------------+--------------
PROD000001 | PostgreSQL
PROD000002 | MySQL
(2 rows)

อ่านวิธีการสร้างตารางในฐานข้อมูลหลายๆ ตัวเพิ่มเติมได้ที่ http://www.eh10.pwp.blueyonder.co.uk/gisq/howto/xcreatepostgres.htm

ใน MySQL ยังไม่มีความสามารถในการสร้างฟังก์ชันเองได้ครับ

8 Reply in this Topic. Dispaly 1 pages and you are on page number 1

1 @R04586
เจอบักเข้าให้แล้วครับ
ถ้ามีการลบข้อมูลใดออก แล้วสร้างเพิ่ม product_id จะไปซ้ำกับค่าในฐานข้อมูลที่มีอยู่แล้ว

เช่น ถ้าข้อมูลที่ใส่แล้วเป็นอย่างนี้

PROD000001 | PostgreSQL
PROD000002 | MySQL

แต่บังเอิญว่า ต้องมีการลบข้อมูลลำดับที่ 1 ออก
จะเหลือ

PROD000002 | MySQL

เมื่อมีการเพิ่มข้อมูลลำดับต่อไป จะได้รหัสซ้ำกับรหัสเดิม จะต้องเกิด Error แน่นอน

ฉะนั้นการใช้ count(*) จะไม่ได้ช่วยอะไรเลยครับ

ผมมาคิดดูว่าน่าจะใช้ Select Case หรือ If Then ช่วย แต่ยังนึกไม่ออกว่าจะทำอย่างไร

ผมเลยแก้ปัญหาแบบลูกทุ่งไปก่อน โดยการใช้ Case Then Else End ช่วยครับ

อันดับแรกต้องเขียนฟังก์ชันขึ้นมาอีกอัน โดยใช้ Max() ช่วย เพื่อใช้ในกรณีที่มีการใส่ข้อมูลลงไปบ้างแล้ว

ผมสร้าง mymax1() อย่างนี้ครับ

mydb=# create or replace function mymax1() returns numeric as '
mydb'# select max(to_number(product_id,''9999999999'')) from products;
mydb'# ' language sql;

แล้วสร้าง mymax2() เพื่อใช้ในกรณีที่ยังไม่มีข้อมูลอยู่เลย

mydb=# create or replace function mymax2() returns bigint as '
mydb'# select count(*) from products;
mydb'# ' language sql;

แล้วค่อยไปแก้ mymax() ตัวเดิมที่เคยสร้างไว้แล้ว ให้เป็น

mydb=# create or replace function mymax() returns numeric as '
mydb'# select case mymax2() when 0 then mymax2() else mymax1() end;
mydb'# ' language sql;

ไม่รู้ว่าจะสามารถนำทั้ง 3 ฟังก์ชันนี้มารวมเป็นหนึ่งเดียวจะได้หรือไม่ คอยติดตามตอนต่อไปนะครับ
2 @R04588
ถ้าเป็นใน MS SQL Server จะทำอย่างไร

ก็เข้าไปสร้างฟังก์ชันเหมือนกันครับ

สร้างฟังก์ชัน mymax() ขึ้นมาก่อน

create function mymax()
returns char(10)
as
begin
declare @chrText varchar(10)
declare @chrText2 varchar(10)
declare @intLen int
set @intLen = (select convert(int,(select max(right(product_id,6)) from products))+1)
set @chrText = replicate('0',6-len(@intLen))+ ltrim(str(@intLen))
if (select count(*) from products)= 0
set @chrText2 = ('PROD000001')
if (select count(*) from products)> 0
set @chrText2 = ('PROD'+ @chrText)
return @chrText2
end

จะสังเกตเห็นว่ามีการใช้ฟังก์ชัน replicate() ซึ่งจะเทียบได้กับ lpad() ของ Postgres หรือ Strint() ของ Access

และ convert() ในการแปลง data type ซึ่งจะเหมือน CInt() ของ Access หรือ to_number() ของ Postgres

อันดับต่อไปสร้างตาราง products ขึ้นมา

create table products
(
product_id char(10) primary key default dbo.mymax(),
product_name varchar(254)
)

จะใช้ syntax คล้ายๆ กันกับ Postgres แต่ให้สังเกตการอ้างชื่อฟังก์ชัน mymax() ครับ

ลองใส่ข้อมูล

insert into products(product_name) values('Postgres')

แล้วลองวิวดู

select * from products

ถ้าต้องการที่จะสร้างอีกเป็น stored procedure เพื่อให้การคีย์ข้อมูลสะดวก ก็สามารถทำได้ ลองสร้างดูเองนะครับ

3 @R04589
มาสร้าง Store Procedure กันต่อดีกว่า

create procedure myadd
@varText varchar(254)
as
Insert Into products(product_name) values(@varText)
go

เวลาเรียกใช้งาน

exec myadd 'MS SQL Server'


4 @R04594
ไม่ทราบว่า d/l PostgreSQL ได้ที่ไหนครับ?
สนใจอยากจะศึกษาครับ
5 @R04596
อีกครั้งน่ะครับ ไม่ทราบว่าผมใช้ OS เป็น winMe winXP สามารถใช้PostgreSQL ได้หรือไม่ครับ
6 @R04597
ลองดูกระทู้ http://www.thai-access.com/suphap.php?topic_id=1732 ครับ พร้อมวิธีการติดตั้ง

ผมลองกับ Windows 2000 ครับ แต่คิดว่า XP น่าจะเล่นได้ ไม่แน่ใจว่าจะเล่นกับ WinMe ได้หรือเปล่าครับ



7 @R04612
มาลองดูใน Oracle บ้างครับ

1. สร้างตารางเหมือนกัน

SQL> create table products
(
product_id char(10) primary key,
product_name varchar(20)
)
;

Table created.

2. ลองดูรายละเอียดของตารางที่เพิ่งจะสร้างขึ้น

SQL> describe products;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
PRODUCT_ID NOT NULL CHAR(10)
PRODUCT_NAME VARCHAR2(254)

จะเห็นว่าใช้คำสั่ง describe คล้ายๆ MySQL (หรือ MySQL เอาของเขาไป?)

3. มาสร้าง stored procedure ซึ่งจะเป็นตัวสำคัญ

CREATE OR REPLACE PROCEDURE my_add (
my_name IN VARCHAR)
AS
my_Text CHAR(10);
my_Count NUMBER;
my_Max CHAR(6);

BEGIN

select count(*)
into my_count
from products;

select lpad(to_number(substr(max(product_id),5)+1),6,'0')
into my_MAX
from products;

select concat('PROD',my_Max)
into my_Text
from dual;

IF (my_count > 0) THEN
insert into products values(my_Text, my_name);
END IF;
IF (my_count = 0) THEN
insert into products values('PROD000001', my_name);
END IF;
END;

ให้นำโค้ดข้างต้นไป paste ลงใน SQL * Pluse

SQL> CREATE OR REPLACE PROCEDURE my_add (
2 my_name IN VARCHAR)
3 AS
4 my_Text CHAR(10);
5 my_Count NUMBER;
6 my_Max CHAR(6);
7
8 BEGIN
9
10 select count(*)
11 into my_count
12 from products;
13
14 select lpad(to_number(substr(max(product_id),5)+1),6,'0')
15 into my_MAX
16 from products;
17
18 select concat('PROD',my_Max)
19 into my_Text
20 from dual;
21
22 IF (my_count > 0) THEN
23 insert into products values(my_Text, my_name);
24 END IF;
25 IF (my_count = 0) THEN
26 insert into products values('PROD000001', my_name);
27 END IF;
28 END;
29 /

ให้สังเกตว่า จะต้องปิดด้วย / เพื่อบอกว่าจบด้วย ไม่งั้นมันจะไม่ยอมสร้างให้สักที่ครับ

ถ้าไม่มีอะไรผิดพลาด เราจะเห็นข้อความนี้

Procedure created.

4. ใส่ข้อมูลแรกดู

SQL> execute my_add('Oracle');

จะเห็นว่า Oracle ใช้คำสั่ง execute แต่ SQL Server ใช้ exec

พร้อมกับเห็นข้อความนี้

PL/SQL procedure successfully completed.

5. ลองดูข้อมูลที่ใส่ลงไป

SQL> select * from products;

PRODUCT_ID PRODUCT_NAME
---------- --------------------
PROD000001 Oracle

ใส่อีกอัน

SQL> execute my_add('PostgreSQL');

PL/SQL procedure successfully completed.

SQL> select * from products;

PRODUCT_ID PRODUCT_NAME
---------- --------------------
PROD000001 Oracle
PROD000002 PostgreSQL

ใน Oracle จะมีฟังก์ชันจัดการ string อยู่หลายๆ ตัว
ตัวแรกที่ผมใช้คือ substr() ซึ่งจะทำงานคล้ายกับ mid() ของ Access ครับ

SQL> select substr(product_id,5) from products;

SUBSTR
------
000001
000002

และ length() จะเหมือน len() ของ Access/SQL Server

SQL> select length(product_id) from products;

LENGTH(PRODUCT_ID)
------------------
10
10

6. คราวนี้มาถึงการใส่ข้อมูลกระโดดไปลำดับที่สูงๆ ดูบ้าง

SQL> insert into products values('PROD001234','Supap Chaiya');

1 row created.

ลองดูผลลัพธ์

SQL> select * from products;

PRODUCT_ID PRODUCT_NAME
---------- --------------------
PROD000001 Oracle
PROD000002 PostgreSQL
PROD001234 Supap Chaiya

และลองใส่ผ่าน stored procedure ที่เราได้สร้างขึ้นครับ

SQL> execute my_add('Basic Eng');

PL/SQL procedure successfully completed.

SQL> select * from products;

PRODUCT_ID PRODUCT_NAME
---------- --------------------
PROD000001 Oracle
PROD000002 PostgreSQL
PROD001234 Supap Chaiya
PROD001235 Basic Eng

จะเห็นว่ามันเพิ่มลำดับได้ถูกต้อง
8 @R04613
มาถึงทีของ Access ของเราบ้าง

1. สร้างตารางก่อน

Sub createtb()
CurrentDb.Execute "create table products " _
& "(product_id text(10) CONSTRAINT MyCon Primary Key, product_name text(254))"
End Sub

เมื่อเขียนเสร็จให้กด F5 ได้เลย
แล้วไปดูในส่วนของ Tables ว่ามีการสร้างตาราง products ให้หรือยัง

2. สร้างฟังก์ชัน เพื่อใช้ในการใส่ข้อมูล

Function myadd(strName As String)
Dim strID As String, lngCount As Long
lngCount = DCount("*", "products")
If lngCount = 0 Then
CurrentDb.Execute "insert into products values('PROD000001','" & strName & "')"
Else
strID = Format(DMax("val(mid([product_id],6)) + 1", "products"), "000000")
strID = "PROD" & strID
CurrentDb.Execute "insert into products values('" & strID & "','" & strName & "')"
End If
End Function

คงไม่ต้องบรรยายกันมาก เพราะคุ้นเคยกับคำสั่งต่างๆ ดีอยู่แล้ว

3. ลองใส่ข้อมูลแรก

ให้กด Ctrl + G เพื่อเปิดหน้า Debug Window
แล้วพิมพ์

? myadd("MS Access")

แล้วให้กด Enter

ไปเปิดดูตาราง products ว่ามีการเพิ่มข้อมูลให้หรือยังครับ

4. ใส่ข้อมูลแบบกระโดด
ให้พิมพ์

currentdb.execute("insert into products values('PROD001234','Supap Chaiya')")

ลงไป แล้วกด Enter

กลับไปดูตาราง

แล้วก็ให้เพิ่มข้อมูลไปอีก 2 รายการดังนี้

? myadd("PostgreSQL")
? myadd("Oracle")

เปิดดูตารางจะได้

product_id product_name
PROD000001 MS Access
PROD001234 Supap Chaiya
PROD001235 PostgreSQL
PROD001236 Oracle

พระเอกต้องมาที่หลังครับ
@ ประกาศใช้งานเว็บบอร์ดใหม่ => บอร์ดเรียนรู้ Access สำหรับคนไทย
แล้วจะใส่ลิ้งอ้างอิงมาที่โพสต์เก่านี้หรือไม่ก็ตามสะดวกครับ
Time: 0.0452s