Hiding SQL in a Stored Procedure

I'm sure you've heard that it is a bad practice to embed SQL in your applications and that all direct SQL access should be encapsulated (or hidden) in stored procedures. I've had people ask me exactly what that means so below is a very simple example of that. I am encapsulating a single table. This is really not that beneficial. Where this gets very powerful is when you are joining many tables or performing complex logic. You can use this method to hide schema complexity from any application that can query data. First I create a simple table and populate it:

create table test_data (
 name text,
 address text,
create_date timestamp );

insert into test_data values (
         'lewis',
        '123 abc st',
        timestamp '2001-01-01 10:00:00');
insert into test_data values (
        'george',
        '456 def dr',
        timestamp '2091-01-01 10:00:00');

postgres=# select * from test_data;
 name  |  address   |     create_date
--------+------------+---------------------
lewis  | 123 abc st | 2001-01-01 10:00:00
george | 456 def dr | 2091-01-01 10:00:00
(2 rows)

Next I create a very simple function. This function accepts two timestamps and returns any records that have a creation data falling within those dates.

CREATE OR REPLACE FUNCTION get_data_by_creation(
     timestamp without time zone, 
     timestamp without time zone)
RETURNS SETOF test_data 
AS
$$ 
  SELECT name, address, create_date
    FROM test_data
    WHERE create_date >= $1
      AND create_date <= $2;
$$
LANGUAGE 'sql' VOLATILE;

And then I select from the function passing in different values as input:

postgres=# select * 
             from get_data_by_creation(
               localtimestamp - interval '10 years', 
               localtimestamp);
 name  |  address   |     create_date
-------+------------+---------------------
 lewis | 123 abc st | 2001-01-01 10:00:00
(1 row)

postgres=# select * 
             from get_data_by_creation(
               localtimestamp, 
               localtimestamp + interval '100 years');
  name  |  address   |     create_date
--------+------------+---------------------
 george | 456 def dr | 2091-01-01 10:00:00
(1 row)

As I said above, this is a very simple example. The usefulness becomes very apparent when you are trying to hide the complexities of your schema from application programmers and users.

postgresql function