в 13:45, 26 мая 2010

2010-05-26T13:45:56Z

Admin: 1 версия

2010-05-26T10:13:53Z

1 версия

Новая страница

==Create a subblock==



<source lang="sql">

postgres=#
postgres=#
postgres=# CREATE FUNCTION somefunc() RETURNS integer AS $$
postgres$# DECLARE
postgres$# quantity integer := 30;
postgres$# BEGIN
postgres$# RAISE NOTICE "Quantity here is %", quantity; -- Quantity here is 30
postgres$# quantity := 50;
postgres$# --
postgres$# -- Create a subblock
postgres$# --
postgres$# DECLARE
postgres$# quantity integer := 80;
postgres$# BEGIN
postgres$# RAISE NOTICE "Quantity here is %", quantity; -- Quantity here is 80
postgres$# END;
postgres$#
postgres$# RAISE NOTICE "Quantity here is %", quantity; -- Quantity here is 50
postgres$#
postgres$# RETURN quantity;
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=#
postgres=# select somefunc();
NOTICE: Quantity here is 30
NOTICE: Quantity here is 80
NOTICE: Quantity here is 50
REATE
somefunc
----------
50
(1 row)
postgres=#
postgres=# drop function somefunc();
DROP FUNCTION
postgres=#
postgres=#

</source>

==SQL Functions Returning Sets==



<source lang="sql">
postgres=#
postgres=# CREATE TABLE myTable (id int,
postgres(# sid int,
postgres(# name text);
CREATE TABLE
postgres=#
postgres=# INSERT INTO myTable VALUES (1, 1, "Joe");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES (1, 2, "Ed");
INSERT 0 1
postgres=# INSERT INTO myTable VALUES (2, 1, "Mary");
INSERT 0 1
postgres=#
postgres=# -- SQL Functions Returning Sets
postgres=#
postgres=# CREATE FUNCTION getData(int) RETURNS SETOF myTable AS $$
postgres$# SELECT * FROM myTable WHERE id = $1;
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT * FROM getData(1) AS t1;
REATE
id | sid | name
----+-----+------
1 | 1 | Joe
1 | 2 | Ed
(2 rows)
postgres=#
postgres=# drop function getData(int);
DROP FUNCTION
postgres=# drop table myTable;
DROP TABLE
postgres=#
postgres=#

</source>

==Two "anyelement" parameters==



<source lang="sql">
postgres=#
postgres=# CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
postgres$# SELECT ARRAY[$1, $2];
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT make_array(1, 2) AS intarray, make_array("a"::text, "b") AS textarray;
REATE
intarray | textarray
----------+-----------
{1,2} | {a,b}
(1 row)
postgres=#
postgres=#
postgres=# drop function make_array(anyelement, anyelement);
DROP FUNCTION
postgres=#

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

PostgreSQL/Postgre SQL/Code Block - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия