PostgreSQL/Store Procedure Function/Function Parameter — различия между версиями

Версия 13:45, 26 мая 2010

Содержание

1 "Anyelement" parameter
2 Here the $1 references the value of the first function argument whenever the function is invoked
3 OUT parameter
4 Output parameters are most useful when returning multiple values
5 Pass constant to function
6 Pass in a whole row
7 Real number parameter
8 Two out parameters
9 Use defined data type as the function parameter

"Anyelement" parameter

postgres=#
postgres=# CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
postgres$#    SELECT $1 > $2;
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT is_greater(1, 2);
   REATE
 is_greater
------------
 f
(1 row)
postgres=#
postgres=# drop function is_greater(anyelement, anyelement);
DROP FUNCTION
postgres=#
postgres=#

Here the $1 references the value of the first function argument whenever the function is invoked

postgres=#
postgres=# CREATE TABLE employee (
postgres(#     ID         int,
postgres(#     name       varchar(10),
postgres(#     salary     real,
postgres(#     start_date date,
postgres(#     city       varchar(10),
postgres(#     region     char(1)
postgres(# );
CREATE TABLE
postgres=#
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (1,  "Jason", 40420,  "02/01/94", "New York", "W");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (2,  "Robert",14420,  "01/02/95", "Vancouver","N");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (3,  "Celia", 24020,  "12/03/96", "Toronto",  "W");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (4,  "Linda", 40620,  "11/04/97", "New York", "N");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (5,  "David", 80026,  "10/05/98", "Vancouver","W");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (6,  "James", 70060,  "09/06/99", "Toronto",  "N");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (7,  "Alison",90620,  "08/07/00", "New York", "W");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (8,  "Chris", 26020,  "07/08/01", "Vancouver","N");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (9,  "Mary",  60020,  "06/09/02", "Toronto",  "W");
INSERT 0 1
postgres=#
postgres=# select * from employee;
 id |  name  | salary | start_date |   city    | region
----+--------+--------+------------+-----------+--------
  1 | Jason  |  40420 | 1994-02-01 | New York  | W
  2 | Robert |  14420 | 1995-01-02 | Vancouver | N
  3 | Celia  |  24020 | 1996-12-03 | Toronto   | W
  4 | Linda  |  40620 | 1997-11-04 | New York  | N
  5 | David  |  80026 | 1998-10-05 | Vancouver | W
  6 | James  |  70060 | 1999-09-06 | Toronto   | N
  7 | Alison |  90620 | 2000-08-07 | New York  | W
  8 | Chris  |  26020 | 2001-07-08 | Vancouver | N
  9 | Mary   |  60020 | 2002-06-09 | Toronto   | W
(9 rows)
postgres=#
postgres=#
postgres=# CREATE FUNCTION myFunction(text) RETURNS employee
postgres-#    AS $$ SELECT * FROM employee WHERE name = $1 $$
postgres-#    LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# -- Here the $1 references the value of the first function argument whenever the function is invoked.
postgres=#
postgres=#
postgres=# select * from myFunction("James");
 id | name  | salary | start_date |  city   | region
----+-------+--------+------------+---------+--------
  6 | James |  70060 | 1999-09-06 | Toronto | N
(1 row)
postgres=#
postgres=#
postgres=# drop function myFunction(text);
DROP FUNCTION
postgres=# drop table employee;
DROP TABLE
postgres=#
postgres=#

OUT parameter

postgres=#
postgres=#
postgres=# CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
postgres$# BEGIN
postgres$#    tax := subtotal * 0.06;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
postgres=#
postgres=# select sales_tax(100);
   REATE
 sales_tax
-----------
         6
(1 row)
postgres=#
postgres=# drop function sales_tax(subtotal real, OUT tax real);
DROP FUNCTION
postgres=#
postgres=#

Output parameters are most useful when returning multiple values

postgres=#
postgres=# -- Output parameters are most useful when returning multiple values
postgres=#
postgres=# CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
postgres$# BEGIN
postgres$#    sum := x + y;
postgres$#    prod := x * y;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=#
postgres=# select sum_n_product(1,2);
     REATE
 sum_n_product
---------------
 (3,2)
(1 row)
postgres=#
postgres=# drop function sum_n_product(x int, y int, OUT sum int, OUT prod int);
DROP FUNCTION
postgres=#
postgres=#

Pass constant to function

postgres=#
postgres=# CREATE FUNCTION "you_me" (integer) RETURNS integer AS "
postgres"#   DECLARE
postgres"#    RENAME $1 TO user_no;
postgres"#     --you INTEGER := 5;
postgres"#   BEGIN
postgres"#     return user_no;
postgres"#   END;" LANGUAGE "plpgsql";
CREATE FUNCTION
postgres=#
postgres=# select you_me(1);
 you_me
--------
      1
(1 row)
postgres=#

Pass in a whole row

postgres=#
postgres=# CREATE TABLE employee (
postgres(#     ID         int,
postgres(#     name       varchar(10),
postgres(#     salary     real,
postgres(#     start_date date,
postgres(#     city       varchar(10),
postgres(#     region     char(1)
postgres(# );
CREATE TABLE
postgres=#
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (1,  "Jason", 40420,  "02/01/94", "New York", "W");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (2,  "Robert",14420,  "01/02/95", "Vancouver","N");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (3,  "Celia", 24020,  "12/03/96", "Toronto",  "W");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (4,  "Linda", 40620,  "11/04/97", "New York", "N");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (5,  "David", 80026,  "10/05/98", "Vancouver","W");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (6,  "James", 70060,  "09/06/99", "Toronto",  "N");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (7,  "Alison",90620,  "08/07/00", "New York", "W");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (8,  "Chris", 26020,  "07/08/01", "Vancouver","N");
INSERT 0 1
postgres=# insert into employee (ID, name,    salary, start_date, city,       region)
postgres-#               values (9,  "Mary",  60020,  "06/09/02", "Toronto",  "W");
INSERT 0 1
postgres=#
postgres=# select * from employee;
                         REATE
 id |  name  | salary | start_date |   city    | region
----+--------+--------+------------+-----------+--------
  1 | Jason  |  40420 | 1994-02-01 | New York  | W
  2 | Robert |  14420 | 1995-01-02 | Vancouver | N
  3 | Celia  |  24020 | 1996-12-03 | Toronto   | W
  4 | Linda  |  40620 | 1997-11-04 | New York  | N
  5 | David  |  80026 | 1998-10-05 | Vancouver | W
  6 | James  |  70060 | 1999-09-06 | Toronto   | N
  7 | Alison |  90620 | 2000-08-07 | New York  | W
  8 | Chris  |  26020 | 2001-07-08 | Vancouver | N
  9 | Mary   |  60020 | 2002-06-09 | Toronto   | W
(9 rows)
postgres=#
postgres=#
postgres=# CREATE FUNCTION concat_selected_fields(in_t employee) RETURNS text AS $$
postgres$# BEGIN
postgres$#    RETURN in_t.ID || in_t.name || in_t.city || in_t.region;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=#
postgres=# select concat_selected_fields(employee.*) from employee;
         REATE
 concat_selected_fields
------------------------
 1JasonNew YorkW
 2RobertVancouverN
 3CeliaTorontoW
 4LindaNew YorkN
 5DavidVancouverW
 6JamesTorontoN
 7AlisonNew YorkW
 8ChrisVancouverN
 9MaryTorontoW
(9 rows)
postgres=#
postgres=#
postgres=# drop function concat_selected_fields(in_t employee);
DROP FUNCTION
postgres=# drop table employee;
DROP TABLE
postgres=#
postgres=#

Real number parameter

postgres=#
postgres=#
postgres=#
postgres=# CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
postgres$# BEGIN
postgres$#    RETURN subtotal * 0.06;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
postgres=#
postgres=#
postgres=# select sales_tax(100);
   REATE
 sales_tax
-----------
         6
(1 row)
postgres=#
postgres=# drop function sales_tax( real);
DROP FUNCTION
postgres=#
postgres=#

Two out parameters

postgres=#
postgres=# CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
postgres-# AS "select $1, array[$1,$1]" LANGUAGE sql;
CREATE FUNCTION
postgres=#
postgres=# SELECT * FROM dup(22);
    REATE
 f2 |   f3
----+---------
 22 | {22,22}
(1 row)
postgres=#
postgres=# drop function dup(f1 anyelement, OUT f2 anyelement, OUT f3 anyarray);
postgres=#
postgres=#

Use defined data type as the function parameter

postgres=#
postgres=# CREATE TYPE inventory_item AS (
postgres(#    name            text,
postgres(#    supplier_id     integer,
postgres(#    price           numeric
postgres(# );
CREATE TYPE
postgres=#
postgres=# CREATE TABLE on_hand (
postgres(#    item      inventory_item,
postgres(#    count     integer
postgres(# );
CREATE TABLE
postgres=#
postgres=# INSERT INTO on_hand VALUES (ROW("fuzzy dice", 42, 1.99), 1000);
INSERT 0 1
postgres=#
postgres=# select * from on_hand;
          item          | count
------------------------+-------
 ("fuzzy dice",42,1.99) |  1000
(1 row)
postgres=#
postgres=#
postgres=# CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
postgres-# AS "SELECT $1.price * $2" LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# select price_extension(ROW("fuzzy dice", 42, 1.99), 1000);
 price_extension
-----------------
         1990.00
(1 row)
postgres=#
postgres=# drop function price_extension(inventory_item, integer);
DROP FUNCTION
postgres=# drop table on_hand;
DROP TABLE
postgres=# drop type inventory_item;
DROP TYPE
postgres=#
postgres=#

PostgreSQL/Store Procedure Function/Function Parameter — различия между версиями

Версия 13:45, 26 мая 2010

Содержание

"Anyelement" parameter

Here the $1 references the value of the first function argument whenever the function is invoked

OUT parameter

Output parameters are most useful when returning multiple values

Pass constant to function

Pass in a whole row

Real number parameter

Two out parameters

Use defined data type as the function parameter

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты

Текущая версия на 10:14, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)	Версия 13:45, 26 мая 2010 (просмотреть исходный код) (обсуждение) Следующая правка →
(нет различий)