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