PostgreSQL/Postgre SQL/Code Block

Create a subblock

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=#

SQL Functions Returning Sets

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=#

Two "anyelement" parameters

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=#