PostgreSQL 8.0.0 �����ĵ���PostgreSQL �й� ������ | ||||
---|---|---|---|---|
Prev | Fast Backward | Chapter 31. ��չ SQL | Fast Forward | Next |
SQL ����ִ��һ������ SQL ��ѯ���б������б������һ����ѯ�Ľ���� ��������һ�� SELECT���ڱȽϼ�����£��Ǽ��ϵ�������� �������һ����ѯ����ĵ�һ�С������ס���н����"��һ��"�Dz���ȷ�ģ��������� ORDER BY �Խ������ ������һ����ѯ���ɲ������У���ô���� NULL ֵ��
���⣬һ�� SQL ������������Ϊ����һ�����ϣ������ǰѸú����ķ�����������Ϊ SETOF sometype�� ���ʱ�����һ����ѯ����������ж��ᱻ���ء������ϸ�������潲��
SQL �����ĺ�����Ӧ����һ���÷ֺŷָ��� SQL �����б� ���һ��������ķֺ��ǿ�ѡ�ġ����Ǻ�������Ϊ���� void�� �������һ���������� SELECT��
�κ� SQL ���Ե�������Դ����һ�𣬶�����µĺ����� ���� SELECT ��ѯ֮�⣬��������������ĵIJ�ѯ ��INSERT��UPDATE���� DELETE���� �Լ����� SQL �����Ψһ���������㲻���� SQL �����﷽ BEGIN��COMMIT��ROLLBACK������ SAVEPOINT ����� ���������һ�����������һ�����غ��������ķ������͵� SELECT ��䡣 ���⣬������붨��һ��ִ��ij�ֶ��������Dz�����ʲô���õ���ֵ�� SQL ������ ��ô����Զ���֮Ϊ���� void�� ��������£��ú����岻���� SELECT ��β�� ���磬������������� emp ��ɾ��������нˮ��
CREATE FUNCTION clean_emp() RETURNS void AS ' DELETE FROM emp WHERE salary < 0; ' LANGUAGE SQL; SELECT clean_emp(); clean_emp ----------- (1 row)
CREATE FUNCTION ������Ҫ������д��һ���ִ��ı��� ͨ����˵���ִ�����ʹ����Ԫ����Χ������Щ������ Section 4.1.2.2���� ��������ʹ��ͨ�����ִ���������������ݺ�������ʹ�õĵ����ţ�'���ͷ�б�ܣ�\'���� ͨ���ķ�����д���ݡ�
SQL �����IJ����ڲ�ѯ������� $n ����ã� $1 ָ��һ��������$2 ָ�ڶ����������Դ����ơ� ��������Ǹ������ͣ���ô�����õ��ʾ���� Ҳ����˵��$1.name�����ʲ�������ֶΡ� �������ֻ����������ֵ�����ܵ�����ʶ��ʹ�á���ˣ�������ô���Ǻ���ģ�
INSERT INTO mytable VALUES ($1);
������ô���Ͳ����ˣ�
INSERT INTO $1 VALUES (42);
��� SQL �������ܾ���û�в������ҷ���һ�� �������ͣ����� integer �ĺ�����
CREATE FUNCTION one() RETURNS integer AS $$ SELECT 1 AS result; $$ LANGUAGE SQL; -- ����һ���ִ��ı������ CREATE FUNCTION one() RETURNS integer AS ' SELECT 1 AS result; ' LANGUAGE SQL; SELECT one(); one ----- 1
��ע�������ں��������涨����һ���ֶα��������ں�������������� result���� �����ֶα����ں��������Dz��ɼ��ġ���ˣ�������� one Ϊ��ǩ�ģ������� result��
����һ�����ܻ��������������� SQL ��������һ���� ��������������ע�������ں������������$1 �� $2 ���ò����ġ�
CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$ SELECT $1 + $2; $$ LANGUAGE SQL; SELECT add_em(1, 2) AS answer; answer -------- 3
������һ�������õĺ��������ǿ���������һ�������ʺ����ۿ������� debit��������
CREATE FUNCTION tf1 (integer, numeric) RETURNS integer AS $$ UPDATE bank SET balance = balance - $2 WHERE acctountno = $1; SELECT 1; $$ LANGUAGE SQL;
һ���û���������������������������ʻ� 17 �ۿ� $100.00��
SELECT tf1( 17,100.0);
ʵ�������ǿ���ϲ��������һ���ȳ��� 1 ������һЩ�Ľ���� ���Ը��п��ܵĶ�����
CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$ UPDATE bank SET balance = balance - $2 WHERE accountno = $1; SELECT balance FROM bank WHERE accountno = $1; $$ LANGUAGE SQL;
�����������µ���
��������дʹ���ø��������������ĺ���ʱ�� ���Dz���Ҫ����������Ҫ�ĸ�����������������ʹ�� $1��$2һ����������Ҫ�����������ֶΣ����������磬 ���� emp ��һ��������Ա��Ϣ�ı��������Ҳ�Ǹñ�ÿ�еĸ������͵����֡��������һ������ double_salary��������ij��нˮ����֮�����ֵ��
CREATE TABLE emp ( name text, salary numeric, age integer, cubicle point ); CREATE FUNCTION double_salary(emp) RETURNS integer AS $$ SELECT $1.salary * 2 AS salary; $$ LANGUAGE SQL; SELECT name, double_salary(emp.*) AS dream FROM emp WHERE emp.cubicle ~= point '(2,1)'; name | dream ------+------- Bill | 8400
��ע������ʹ�� $1.salary ���ѡ���������ֵ��һ���ֶΡ� ��Ҫע��SELECT���������ʹ�� * ��ʾ�ñ��������ǰ����Ϊ������ֵ�� ���������Ҳ�����ñ��������ã�������������
SELECT name, double_salary(emp) AS dream FROM emp WHERE emp.cubicle ~= point '(2,1)';
��������÷��Ѿ������ˣ���Ϊ����������
��ʱ�����Ƕ�̬�ع���һ�����ϲ���ֵ�����á� ���ǿ����� ROW ������ʵ��������ܡ� ���磬���ǿ��Ե��ڴ��ݸ����������ݣ�
SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream FROM emp;
����Ҳ����дһ�����ظ������͵ĺ����� ������һ������һ�� emp ���������ӣ�
CREATE FUNCTION new_emp() RETURNS emp AS $$ SELECT text 'None' AS name, 1000.0 AS salary, 25 AS age, point '(2,2)' AS cubicle $$ LANGUAGE SQL;
��������������Ǹ�ÿ���ֶζ�������һ�������� ��Ȼ���ǿ������κμ������ʽ��������Щ������
ע�ⶨ�庯����������Ҫ�����⣺
ѡ���б��˳��������ø���������صı����ֶε�˳����ȫһ���� �������������������ֶε�������ϵͳ�ء���
�����Ա��ʽ��������ת����ƥ�临�����͵Ķ��塣 �����㽫��������Ĵ�����Ϣ��
ERROR: function declared to return emp returns varchar instead of text at column 1
����һ������ͬ�������ķ����ǣ�
CREATE FUNCTION new_emp() RETURNS emp AS $$ SELECT ROW('None', 1000.0, 25, '(2,2)')::emp; $$ LANGUAGE SQL;
��������дһ���÷���һ����Ӧ���������ֶε� SELECT�� ����������£���ô����û���κκô�����������ijЩ������һ���ܺ��õĶ��� — ���磬���������Ҫͨ����������һ��������Ҫ�ĸ���������ֵ�ĺ�������������
���ǿ��������ַ�����ֱ�ӵ������������
SELECT new_emp(); new_emp -------------------------- (None,1000.0,25,"(2,2)") SELECT * FROM new_emp(); name | salary | age | cubicle ------+--------+-----+--------- None | 1000.0 | 25 | (2,2)
�ڶ��ַ����� Section 31.4.3 ���и�������������
��ʹ��һ�����ظ������͵ĺ�����ʱ���������ӽ����ֻ��ȡһ���ֶΡ� �������������������
SELECT (new_emp()).name; name ------ None
������Ҫһ�������Բ�����Է�ֹ��������⡣ �������ʡ�������������ῴ���������������Ķ�����
SELECT new_emp().name; ERROR: syntax error at or near "." at character 17 LINE 1: SELECT new_emp().name; ^
����һ��ѡ����ʹ�ú�����ʾ�������ֶγ�ȡ��������Щ����ļ���������ͨ������ʹ��attribute(table)�� table.attribute�ı�ʾ����
SELECT name(new_emp()); name ------ None
-- -- ���������仰��ͬ�� -- SELECT emp.name AS youngster FROM emp WHERE emp.age < 30; -- SELECT name(emp) AS youngster FROM emp WHERE age(emp) < 30; youngster ----------- Sam Andy
��ʾ: ������ʾ�����ֶ����Ա�ʾ��֮��ĵ�Ч��ϵ�����ǿ���ʹ�ø��������ϵĺ�����ģ��"����ó����ֶ�"�� ���磬ʹ��ǰ��� double_salary(emp) ���壬 ���ǿ���д
SELECT emp.name, emp.double_salary FROM emp;Ӧ�ÿ�����ô�ö�������ȷ֪�� double_salary �����DZ���һ����ʵ���ֶΡ� ����Ҳ����ģ����ͼ�ϵļ���ó����ֶΡ���
����һ��ʹ�ú��������н��������ǰѽ�����ݸ�����һ��������������ĺ�����
CREATE FUNCTION getname(emp) RETURNS text AS $$ SELECT $1.name; $$ LANGUAGE SQL; SELECT getname(new_emp()); getname --------- None (1 row)
����һ����ʹ�÷��ظ������͵ĺ����ķ����ǰ�������һ������ʹ�ã�����������
���� SQL �����������ڲ�ѯ�� FROM �Ӿ���ʹ�á� ���������ڷ��ظ������͵ĺ����ر����á�����ú�������Ϊ����һ���������ͣ� ��ô��������һ�����ֶα�����ú�������Ϊ����һ���������ͣ���ô�ñ�������һ������������ÿ��������ɵ��С�
������һ�����ӣ�
CREATE TABLE foo (fooid int, foosubid int, fooname text); INSERT INTO foo VALUES (1, 1, 'Joe'); INSERT INTO foo VALUES (1, 2, 'Ed'); INSERT INTO foo VALUES (2, 1, 'Mary'); CREATE FUNCTION getfoo(int) RETURNS foo AS $$ SELECT * FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; SELECT *, upper(fooname) FROM getfoo(1) AS t1; fooid | foosubid | fooname | upper -------+----------+---------+------- 1 | 1 | Joe | JOE (2 rows)
�����������ʾ�����������ǿ�����Դ�һ����ͨ����ֶ�һ���Դ������Ľ���ֶΡ�
��ע������ֻ�Ӹú����л�ȡ��һ�С�������Ϊ����û��˵ SETOF�� ���������������һ��������
���һ�� SQL ��������Ϊ���� SETOF sometype�� ��ʱ�ú���������SELECT��ѯһֱִ�е������������������ÿ�ж������ý�����ϵ�һ��Ԫ�ط��ء�
�������ͨ�����ڰѺ�������FROM�Ӿ�����õ�ʱ�� ���ʱ�������ص�ÿһ�ж���Ϊ��ѯ�ɼ��ĸñ��һ�С� ���磬����� foo ���ź�����һ�������ݣ�������˵��
CREATE FUNCTION getfoo(int) RETURNS setof foo AS $$ SELECT * FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; SELECT * FROM getfoo(1) AS t1;
�������ǵõ���
fooid | foosubid | fooname -------+----------+--------- 1 | 1 | Joe 1 | 2 | Ed (2 rows)
Ŀǰ�����ؼ��ϵĺ���Ҳ������һ����ѯ��ѡ���б�����á� ���ڸò�ѯ�Լ����ɵ�ÿһ�У��������������ؼ��ϵĺ����� ������Ըú����Ľ�����е�ÿ��Ԫ�ض�������һ������С������� ��ע�⣬��������Ѿ������ˣ��ڽ����İ汾�п��ܻᱻɾ���� �������һ����ѡ���б���ʹ�÷��ؼ��ϵĺ��������ӣ�
CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$ SELECT name FROM nodes WHERE parent = $1 $$ LANGUAGE SQL; SELECT * FROM nodes; name | parent -----------+-------- Top | Child1 | Top Child2 | Top Child3 | Top SubChild1 | Child1 SubChild2 | Child1 (6 rows) SELECT listchildren('Top'); listchildren -------------- Child1 Child2 Child3 (3 rows) SELECT name, listchildren(name) FROM nodes; name | listchildren --------+-------------- Top | Child1 Top | Child2 Top | Child3 Child1 | SubChild1 Child1 | SubChild2 (5 rows)
������SELECT���ע��û�г���Child2�� Child3�ȵ��С� ������Ϊlistchildren Ϊ��Щ��������һ���ռ��ϣ� ��˲������κν���С�
SQL ������������Ϊ���ܲ����ض�̬������ anyelement �� anyarray������ Section 31.2.5 ��ȡ�йض�̬�����ĸ���ϸ�ڡ� ������һ����̬�ĺ��� make_array����������������������Ԫ���н���һ�����飺
CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$ SELECT ARRAY[$1, $2]; $$ LANGUAGE SQL; SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray; intarray | textarray ----------+----------- {1,2} | {a,b} (1 row)
��ע������ʹ��������ת�� 'a'::text �������������� text�� �������ֻ��һ���ִ��ı�������Ҫ��Ķ������������ͻᱻ�������� unknown�� ���� unknown ����һ����Ч�����͡����û������ת������ô�ͻῴ���������������Ĵ�����Ϣ��
ERROR: could not determine "anyarray"/"anyelement" type because input has type "unknown"
PostgreSQL �����ж�̬�IJ����ĺ�������һ���̶����ͣ����Ƿ��������С����磺
CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$ SELECT $1 > $2; $$ LANGUAGE SQL; SELECT is_greater(1, 2); is_greater ------------ f (1 row) CREATE FUNCTION invalid_func() RETURNS anyelement AS $$ SELECT 1; $$ LANGUAGE SQL; ERROR: cannot determine result data type DETAIL: A function returning "anyarray" or "anyelement" must have at least one argument of either type.