The Art of PostgreSQL/

...

/

Stored Procedures: A Data Access API

Stored Procedures: A Data Access API

Learn about stored procedures using coding examples.

We'll cover the following...

When using PostgreSQL, it’s also possible to create server-side functions. Those SQL objects store code and then execute it when called.

How to create stored procedures?

In this section, you’ll learn how to create stored procedures.

The naïve way

The naïve way to create a server-side stored procedure from our current example would be the following:

PostgreSQL
create or replace function get_all_albums
 (
   in  name     text,
   out album    text,
   out duration interval
 )
returns setof record
language sql
as $$
  select album.title as album,
         sum(milliseconds) * interval '1 ms' as duration
    from album
         join artist using(artistid)
         left join track using(albumid)
   where artist.name = get_all_albums.name
group by album
order by album;
$$;

But giving the name of the artist rather than it’s artistid means that the function won’t be efficient to use and for no good reason.

The improved version

So, instead, we’re going to define a better version that works with an artist ID:

PostgreSQL
create or replace function get_all_albums
 (
   in  artistid bigint,
   out album    text,
   out duration interval
 )
returns setof record
language sql
as $$
  select album.title as album,
         sum(milliseconds) * interval '1 ms' as duration
    from album
         join artist using(artistid)
         left join track using(albumid)
   where artist.artistid = get_all_albums.artistid
group by album
order by album;
$$;

This function is written in PL/SQL, so it’s basically a SQL query that accepts parameters. To run it, simply use the following query:

PostgreSQL
select * from get_all_albums(127);

Of course, if we only have the name of the artist we’re interested in, we don’t need to first do another query.

Using nested query

We can directly fetch the artistid from a subquery:

PostgreSQL
select *
  from get_all_albums(
        (select artistid
           from artist
          where name = 'Audioslave')
       );

As we can see, the ...