PostgreSQL/Store Procedure Function/Function Return

Материал из SQL эксперт
Перейти к: навигация, поиск

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

   <source lang="sql">

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

      </source>
   
  


Define function to add two parameters together

   <source lang="sql">

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

      </source>
   
  


Return a table from a function

   <source lang="sql">

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


      </source>
   
  


Return "double" from function

   <source lang="sql">

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

      </source>
   
  


Return entire row

   <source lang="sql">

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

      </source>
   
  


Returning a concatenated string

   <source lang="sql">

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

      </source>
   
  


Using the result set returned from the function

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

      </source>