PostgreSQL - Select All Results From Many-To-Many

Viewed 15

I have a simple set of tables used for a many-to-many relationship - a book can have many authors, an author can have many books:

CREATE TABLE book (
  book_id serial PRIMARY KEY,  -- implicit primary key constraint
  book_name text NOT NULL
);

CREATE TABLE author (
  author_id  serial PRIMARY KEY,  -- implicit primary key constraint
  author_name text NOT NULL
);

CREATE TABLE book_author (
  book_author_id  serial PRIMARY KEY,
  book_id int,
  author_id numeric NOT NULL
);

I can query for a particular book to get all the authors:

SELECT 
    author_name 
from 
    author a 
where 
    a.book_id = 23

But there could be any number of rows returned, because there could be any number of authors for the book.

What I want is a way to format the result (no matter how many rows) into a nicely formatted string (like "Robert Heinlein, Arthur C. Clarke, Ray Bradbury").

Is this possible in postgreSQL?

0 Answers
Related