PostgreSQL/Postgre SQL/RECORD

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

Define and use record type

   <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=# postgres=# select * from books;

 id  |   title    | author_id | subject_id

+------------+-----------+------------
7808 | Java       |      4156 |          9
4513 | Javascript |      1866 |         15
4267 | C#         |      2001 |         15

(3 rows) postgres=# postgres=# drop function extract_title(integer); DROP FUNCTION postgres=# postgres=# CREATE FUNCTION "extract_title" (integer) RETURNS text AS " postgres"# DECLARE postgres"# sub_id ALIAS FOR $1; postgres"# text_output TEXT :=""""; postgres"# row_data RECORD; postgres"# BEGIN postgres"# FOR row_data IN SELECT * FROM books postgres"# WHERE subject_id = sub_id ORDER BY title LOOP postgres"# text_output := row_data.title; postgres"# END LOOP; postgres"# RETURN text_output; postgres"# END; postgres"# " LANGUAGE "plpgsql"; CREATE FUNCTION postgres=# postgres=# select extract_title(15);

extract_title

Javascript

(1 row) postgres=# postgres=# drop table books; DROP TABLE postgres=# postgres=#

      </source>