PostgreSQL/Store Procedure Function/Function Return

A SQL function that returns a book title based on the ID number passed to the function

postgres=#
postgres=# CREATE TABLE "books" (
postgres(#      "id"           integer NOT NULL,
postgres(#      "title"        text NOT NULL,
postgres(#      "author_id"    integer,
postgres(#      "subject_id"   integer,
postgres(#      Constraint "books_id_pkey" Primary Key ("id")
postgres(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "books_id_pkey" for table "books"
CREATE TABLE
postgres=#
postgres=#
postgres=# insert into books values (7808,  "Java",                  4156, 9);
INSERT 0 1
postgres=# insert into books values(4513,  "Javascript",            1866, 15);
INSERT 0 1
postgres=# insert into books values(4267,  "C#",                    2001, 15);
INSERT 0 1
postgres=# insert into books values(1608,  "Oracle",                1809, 2);
INSERT 0 1
postgres=# insert into books values(1590,  "Sql Server",            1809, 2);
INSERT 0 1
postgres=# insert into books values(25908, "Postgre SQL",          15990, 2);
INSERT 0 1
postgres=# insert into books values(1501,  "Python",                2031, 2);
INSERT 0 1
postgres=# insert into books values(190,   "Java by API",             16, 6);
INSERT 0 1
postgres=# insert into books values(1234,  "2D",                   25041, 3);
INSERT 0 1
postgres=# insert into books values(2038,  "C",                     1644, 0);
INSERT 0 1
postgres=# insert into books values(156,   "C++",                    115, 9);
INSERT 0 1
postgres=# insert into books values(41473, "Programming Python",    7805, 4);
INSERT 0 1
postgres=# insert into books values(41477, "Learning Python",       7805, 4);
INSERT 0 1
postgres=# insert into books values(41478, "Perl Cookbook",         7806, 4);
INSERT 0 1
postgres=# insert into books values(41472, "Practical PostgreSQL",  1212, 4);
INSERT 0 1
postgres=#
postgres=# -- A SQL function that returns a book title based on the ID number passed to the function
postgres=#
postgres=# CREATE FUNCTION title(integer) RETURNS text
postgres-# AS "SELECT title from books where id = $1"
postgres-# LANGUAGE "sql";
CREATE FUNCTION
postgres=#
postgres=# SELECT title(41472) AS book_title;
      book_title
----------------------
 Practical PostgreSQL
(1 row)
postgres=#
postgres=# drop function title(integer);
DROP FUNCTION
postgres=# drop table books;
DROP TABLE
postgres=#
postgres=#

Define function to add two parameters together

postgres=#
postgres=# CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
postgres$#    SELECT $1 + $2;
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT add_em(1, 2) AS answer;
 answer
--------
      3
(1 row)
postgres=#
postgres=# drop function add_em(integer, integer);
DROP FUNCTION
postgres=#
postgres=#

Return a table from a function

postgres=# CREATE TABLE myTable (
postgres(#          id int,
postgres(#          sid int,
postgres(#          name text);
CREATE TABLE
postgres=#
postgres=# insert into myTable values(1,2,"a");
INSERT 0 1
postgres=# insert into myTable values(2,3,"b");
INSERT 0 1
postgres=#
postgres=# select * from myTable;
 id | sid | name
----+-----+------
  1 |   2 | a
  2 |   3 | b
(2 rows)
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;
 id | sid | name
----+-----+------
  1 |   2 | a
(1 row)
postgres=#
postgres=# drop function getData(int);
DROP FUNCTION
postgres=# drop table myTable;
DROP TABLE
postgres=#

Return "double" from function

postgres=# CREATE FUNCTION "double_price" (double precision) RETURNS double precision AS "
postgres"#   DECLARE
postgres"#   BEGIN
postgres"#     return $1 * 2;
postgres"#   END;
postgres"# " LANGUAGE "plpgsql";
CREATE FUNCTION
postgres=#
postgres=# select double_price(10);
 double_price
--------------
           20
(1 row)
postgres=#

Return entire row

postgres=#
postgres=# CREATE TABLE emp (
postgres(#    name        text,
postgres(#    salary      numeric,
postgres(#    age         integer,
postgres(#    cubicle     point
postgres(# );
CREATE TABLE
postgres=#
postgres=#
postgres=# insert into emp values ("None", 1000.0, 25, "(2,2)");
INSERT 0 1
postgres=#
postgres=# CREATE FUNCTION new_emp() RETURNS emp AS $$
postgres$#    SELECT ROW("None", 1000.0, 25, "(2,2)")::emp;
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# select new_emp();
         new_emp
--------------------------
 (None,1000.0,25,"(2,2)")
(1 row)
postgres=#
postgres=# drop function new_emp();
DROP FUNCTION
postgres=# drop table emp;
DROP TABLE
postgres=#
postgres=# \

Returning a concatenated string

postgres=#
postgres=#
postgres=# -- Returning a concatenated string
postgres=#
postgres=# CREATE FUNCTION compound_word(text, text) RETURNS text AS "
postgres"#   DECLARE
postgres"#
postgres"#      -- Define aliases for function arguments.
postgres"#     word1 ALIAS FOR $1;
postgres"#     word2 ALIAS FOR $2;
postgres"#
postgres"#   BEGIN
postgres"#
postgres"#      -- Return the resulting joined words.
postgres"#     RETURN word1 || word2;
postgres"#
postgres"#   END;
postgres"#
postgres"# " LANGUAGE "plpgsql";
ERROR:  function "compound_word" already exists with same argument types
postgres=#
postgres=# SELECT compound_word("break", "fast");
 compound_word
---------------
 breakfast
(1 row)
postgres=#

Using the result set returned from the function

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=# CREATE FUNCTION getData(int) RETURNS myTable AS $$
postgres$#    SELECT * FROM myTable WHERE id = $1;
postgres$# $$ LANGUAGE SQL;
CREATE FUNCTION
postgres=#
postgres=# SELECT *, upper(name) FROM getData(1) AS t1;
          REATE
 id | sid | name | upper
----+-----+------+-------
  1 |   1 | Joe  | JOE
(1 row)
postgres=#
postgres=# drop function getData(int);
DROP FUNCTION
postgres=# drop table myTable;
DROP TABLE
postgres=#
postgres=#