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