Home
About Me

Types From Tables in PL/pgSQL

In PL/pgSQL, functions can reuse types of columns in tables.

1
create table books (
2
  id serial primary key,
3
  title text,
4
  author text
5
);
6


7
create or replace function print_title(id integer)
8
  returns void
9
  as $$
10
declare
11
  title_to_print books.title%type;
12
begin
13
  select title
14
  into title_to_print
15
  from books
16
  where books.id = $1;
17


18
  raise notice 'Title: %', title_to_print;
19
end
20
$$ language plpgsql;
21


22
insert into books (title, author)
23
values ('The Great Gatsby', 'F. Scott Fitzgerald');
24


25
select print_title(1);
26
-- NOTICE:  Title: The Great Gatsby

Similarly, you can hold the entire row in a variable.

1
create or replace function print_book(id integer)
2
  returns void
3
  as $$
4
declare
5
  book_to_print books%rowtype;
6
begin
7
  select *
8
  into book_to_print
9
  from books
10
  where books.id = $1;
11


12
  raise notice 'Book Row: %', book_to_print;
13
end
14
$$ language plpgsql;
15


16
select print_book(1);
17
-- NOTICE:  Book Row: (1,"The Great Gatsby","F. Scott Fitzgerald")

Note that %rowtype is meant for variable declaration, not for return types.

In a Return Type

If you’d like the return type of a function to be a table’s row type, you can use returns setof table_name.

1
create or replace function get_book(id integer)
2
  returns setof books
3
  as $$
4
begin
5
  return query
6
    select *
7
    from books
8
    where books.id = $1;
9
end
10
$$ language plpgsql;
11


12
select * from get_book(1);

idtitleauthor
1The Great GatsbyF. Scott Fitzgerald

The record type

You can also use type record, which is a row type with no predefined structure.

1
create or replace function print_book_record(id integer)
2
  returns void
3
  as $$
4
declare
5
  book_to_print record;
6
begin
7
  select *
8
  into book_to_print
9
  from books
10
  where books.id = $1;
11


12
  raise notice 'Book Row: %', book_to_print;
13
end
14
$$ language plpgsql;
15


16
select print_book_record(1);
17
-- NOTICE:  Book Row: (1,"The Great Gatsby","F. Scott Fitzgerald")

Wow! You read the whole thing. People who make it this far sometimes want to receive emails when I post something new.

I also have an RSS feed.