Mega Code Archive

Using the %TYPE attribute

postgres=# 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=# drop function get_author(text); DROP FUNCTION postgres=# postgres=# -- Using the %TYPE attribute postgres=# postgres=# CREATE FUNCTION get_author (text) RETURNS text AS ' postgres'# DECLARE postgres'# -- Declare an alias for the function argument, postgres'# -- which should be the first name of an author. postgres'# f_name ALIAS FOR $1; postgres'# l_name authors.last_name%TYPE; postgres'# BEGIN postgres'# SELECT INTO l_name last_name FROM authors WHERE first_name = f_name; postgres'# postgres'# return f_name || '' '' || l_name; postgres'# postgres'# END; postgres'# ' LANGUAGE 'plpgsql'; CREATE FUNCTION postgres=# postgres=# -- Results of the get_author(?) function postgres=# postgres=# SELECT get_author('Jason'); get_author -------------- Jason Martin (1 row) postgres=# postgres=# drop table authors; DROP TABLE postgres=# postgres=#