PostgreSQL/Postgre SQL/RECORD
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>