Using the %ROWTYPE attribute
postgres=# postgres=# postgres=# CREATE TABLE "authors" ( postgres(# "id" integer NOT NULL, postgres(# "last_name" text, postgres(# "first_name" text, postgres(# Constraint "authors_pkey" Primary Key ("id") postgres(# ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors" CREATE TABLE postgres=# postgres=# postgres=# insert into authors values (1111, 'Martin', 'Jason'); INSERT 0 1 postgres=# insert into authors values (1212, 'Worsley', 'Robert'); INSERT 0 1 postgres=# insert into authors values (15990, 'Mathews', 'John'); INSERT 0 1 postgres=# insert into authors values (25041, 'Smith', 'Williams'); INSERT 0 1 postgres=# insert into authors values (16, 'Alcott', 'May'); INSERT 0 1 postgres=# insert into authors values (4156, 'King', 'Stephen'); INSERT 0 1 postgres=# insert into authors values (1866, 'Herbert', 'Margaret'); INSERT 0 1 postgres=# insert into authors values (1644, 'Hogarth', 'Celia'); INSERT 0 1 postgres=# insert into authors values (2031, 'Brown', 'Wise'); INSERT 0 1 postgres=# insert into authors values (115, 'Poe', 'Allen'); INSERT 0 1 postgres=# insert into authors values (7805, 'Lutz', 'Mark'); INSERT 0 1 postgres=# insert into authors values (7806, 'Rice', 'Tom'); INSERT 0 1 postgres=# insert into authors values (1533, 'Black', 'Chris'); INSERT 0 1 postgres=# insert into authors values (1717, 'Brite', 'Linda'); INSERT 0 1 postgres=# insert into authors values (2112, 'Larry', 'Edward'); INSERT 0 1 postgres=# insert into authors values (2001, 'Clarke', 'Alison'); INSERT 0 1 postgres=# insert into authors values (1213, 'Green', 'Mary'); INSERT 0 1 postgres=# postgres=# select * from authors; id | last_name | first_name -------+-----------+------------ 1111 | Martin | Jason 1212 | Worsley | Robert 15990 | Mathews | John 25041 | Smith | Williams 16 | Alcott | May 4156 | King | Stephen 1866 | Herbert | Margaret 1644 | Hogarth | Celia 2031 | Brown | Wise 115 | Poe | Allen 7805 | Lutz | Mark 7806 | Rice | Tom 1533 | Black | Chris 1717 | Brite | Linda 2112 | Larry | Edward 2001 | Clarke | Alison 1213 | Green | Mary (17 rows) postgres=# postgres=# postgres=# postgres=# -- Using the %ROWTYPE attribute postgres=# postgres=# CREATE FUNCTION get_author (integer) RETURNS text AS ' postgres'# DECLARE postgres'# postgres'# -- Declare an alias for the function argument, postgres'# -- which should be the id of the author. postgres'# author_id ALIAS FOR $1; postgres'# postgres'# found_author authors%ROWTYPE; postgres'# postgres'# BEGIN postgres'# SELECT INTO found_author * FROM authors WHERE id = author_id; postgres'# postgres'# -- Return the first postgres'# RETURN found_author.first_name || '' '' || found_author.last_name; postgres'# postgres'# END; postgres'# ' LANGUAGE 'plpgsql'; CREATE FUNCTION postgres=# postgres=# -- Results of the new get_author() function postgres=# postgres=# SELECT get_author(1212); get_author ---------------- Robert Worsley (1 row) postgres=# postgres=# postgres=# drop table authors; DROP TABLE postgres=# postgres=#
1. | ROWTYPE assign through 'select into' | ||
2. | Using the FOR loop with %ROWTYPE |