Define and use record type
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=#
Related examples in the same category