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