31.4. ��ѯ���ԣ�SQL������

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);

31.4.1. ���������ϵ� SQL ����

��򵥵� 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;

���޸��������µ���

31.4.2. �������͵�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;

��������������Ǹ�ÿ���ֶζ�������һ�������� ��Ȼ���ǿ������κμ������ʽ��������Щ������

ע�ⶨ�庯����������Ҫ�����⣺

����һ������ͬ�������ķ����ǣ�

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)

����һ����ʹ�÷��ظ������͵ĺ����ķ����ǰ�������һ������ʹ�ã�����������

31.4.3. ����������Դ�� SQL ����

���� 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�� ���������������һ��������

31.4.4. ���ؼ��ϵ� SQL ����

���һ�� 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 Ϊ��Щ��������һ���ռ��ϣ� ��˲������κν���С�

31.4.5. ��̬�� SQL ����

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.