A SQL function that returns a book title based on the ID number passed to the function : Function Return « Store Procedure Function « PostgreSQL






  1. PostgreSQL
  2. Store Procedure Function
  3. 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=#








Related examples in the same category

1.Define function to add two parameters together
2.Return entire row
3.Using the result set returned from the function
4.Returning a concatenated string
5.Return 'double' from function
6.Return a table from a function