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

Programming Postgres

This is just a quick overview of the availability of programming for Postgres. With Oracle, your choice in the database is Java or PL/SQL. In general, PL/SQL is the preferred language. One of the things that attracted me to Postgres was the ability to choose one of many languages to program in. I usually choose PL/pgSQL but if I can't do something with that, it's nice to be able to fall back to TCL, Perl or one of the many other languages supported by Postgres. Here is a (incomplete, I'm sure) list of supported languages:

• PL/pgSQL - Standard Procedural Language for Postgres
• PL/TCL - TCL, Tool Command Language, easy to learn but powerful
• PL/Perl - Practical Extraction and Report Language, has grown way beyond its origins
• PL/Java - Java, meh
• PL/PHP - PHP: Hypertext Preprocessor, over hyped language
• PL/Python - Object Oriented, dynamic language. Almost as over hyped as PHP
• PL/R - Statistical Language, never used it.
• PL/Ruby - Great language. Over hyped maybe but is my current favorite open source language
• PL/Scheme - Lispy language, never used it
• PL/sh - Access to your OS shell language

Adding a new language to your existing database is very easy. You can get the details from the Postgres CREATE LANGUAGE syntax docs. The way you will most often add a language is: CREATE LANGUAGE For example: CREATE LANGUAGE pltcl; The language must already be installed in the OS. You will use the dynamic libraries from the language to create and run your programs. You also need the Postgres component for your language. PL/pgSQL, PL/TCL and PL/Perl come with the standard distribution of Postgres. You can get other languages at http://pgfoundry.org/. Run a search on pgFoundry to see what they have.

postgresql programming PL/pgSQL PL/TCL PL/Perl PL/Java language postgres

Foxpro to Postgres Data Converter

Do you remember Foxpro? xBase? Clipper? I'm an old clipper head from way back. Anyway, I ran across something in my recent internet travels that caught my fancy. It may have the longest name of any utility I have ever seen. FoxPro PostgreSQL Import, Export & Convert Software will transfer tables to and from PostgreSQL and FoxPro. I haven't downloaded it yet so I can say how well it works but it's a neat idea. I'm guessing it can handle DBF files so that would make it compatible with dBase, Foxpro, Clipper and may other tools. It's a shareware program and I don't see a price. It's been a while since I even ran across shareare. That's a flashback for me too. I use to write and sell shareware. Anyway, here's the blurb for it:

This application will allow you to transfer tables to and from PostgreSQL and FoxPro databases. Importing and exporting can be done without SQL knowledge. This program saves time if you need to transfer tables between the two databases quickly. Connect via DBC file or multiple DBF files in a folder.

Take care, LewisC

postgresql foxpro

Calling a Procedure or Function in Postgres

I think PL/SQL programmers who move to Postgres all run into the same thing, how do I run the procedure or function once I've created it?

Obviously, it's easy when you are calling it from another stored procedure or function.  What most mean, is how do I call it from the command line?  That's easy too.

Using my function and procedure from earlier posts, i.e. 10 Steps to Creating a Function In PostgreSQL Using PLpgSQL and Creating a Procedure In PostgreSQL Using PLpgSQL.

To call either the function or the procedure, you can run:

SELECT func_1();

Or

SELECT * FROM func_1();

You must include the ().

Same for the procedure.

SELECT proc_1();

Or

SELECT * FROM proc_1();

Of course, if you are using a GUI tool like PgAdmin III, it's a somewhat different story.  You need to open the query window but the syntax will be the same.

Technorati Tags: postgres, postgresql, procedure, create, declare, plpgsql, function, code, develop

Creating a Procedure In PostgreSQL Using PLpgSQL

I recently wrote about creating a function in PL/pgSQL, 10 Steps to Creating a Function In PostgreSQL Using PLpgSQL.

Today, I am going to show you how to create a procedure.  You don't really create a procedure, you use the same basic syntax as you do for a function.  The RETURNS keyword can get a little tricky as that keyword requirement changes based on what you are trying to do.  To start, I will create a procedure that functions the same as the function in the previous entry.  Then I will make some changes.

CREATE OR REPLACE FUNCTION proc_1(
  OUT out_parameter CHAR VARYING(25) )
AS $$
DECLARE
BEGIN
  SELECT datname
    INTO out_parameter
    FROM pg_database
    LIMIT 1;

  RETURN;
END;
$$ LANGUAGE plpgsql;

If you compare this to the function from the previous entry some things jump out right away.  There is no RETURNS statement.  Postgres is able to determine the return type from the OUT parameter.

The DECLARE keyword is completely optional.

The RETURN keyword has no operand.  I don't need to RETURN a variable because the OUT parameter will be the return value.  As a matter of a fact, if I try to RETURN a value, I will get an error.

Below is another procedure that does basically the same thing but does not return a value.  I am showing you this just to show you how to declare a procedure that truly acts as a procedure (that does not return any values).

CREATE OR REPLACE FUNCTION proc_3()
RETURNS void
AS $$
DECLARE
  local_char_var CHAR(30);
BEGIN
  SELECT datname
    INTO local_char_var
    FROM pg_database
    LIMIT 1;

  RETURN;
END;
$$ LANGUAGE plpgsql;

Notice in this procedure that I am declare the return value as RETURNS void.  If you are a C, Java or C# programmer this should be very familiar.

For a PL/SQL programmer, just consider that a FUNCTION RETURNS VOID is the same as a PROCEDURE in PL/SQL.

That's it for this post.  If you have anything specific that you would like me to cover, please leave a comment or drop me an email.

Thanks,

LewisC

 

Technorati Tags: postgres, postgresql, procedure, create, declare, plpgsql, function, code, develop

Should PostgreSQL be renamed and of so, to what?

For, I guess, about the last week or so, there has been an ongoing debate on the Advocacy email list about renaming the software from PostgreSQL to Postgres or something else.

Some people have very strong opinions about it. Personally, I don't see what the big deal is. The only people who really have an issue are the people in that (hacker) community. CEOs don't care what it's called. No developer I know cares what it's called.

Just to clarify, when I say developer, I mean a person who develops software for business use. Not someone who actually writes code for the database itself (those are the hackers).

So what I mean, is that the guy writing a GL, using PostgreSQL, doesn't care if it's PostgreSQL, Postgres, Postres QL, pg, pgsql or anything else. They just want the software to work.

At the beginning of the discussion on the mail list someone complained about people mis-pronouncing the name, PostgreSQL. Que? How can you mis-pronounce a made up name? It's an imaginary word. If I want to pronounce it "frank" am I wrong? Maybe in your universe but not in mine.

Many people have suggested to just say that Postgres is the preferred spelling (and pronunciation) and PostgreSQL is less preferred but acceptable spelling. I would have to agree with that one. If the pronunciation really bothers someone, let them write a blog about it. C'est la vie.

LewisC

postgresql postgres naming opensource

10 Steps to Creating a Function In PostgreSQL Using PLpgSQL

It's actually fairly easy to create a function using PLpgSQL, especially if you are coming from a database background like Oracle or DB2. Both have procedural languages that look a lot like PLpgSQL. I'll go ahead and show you the code for a very basic function and then I'll explain the steps individually.

CREATE OR REPLACE FUNCTION func_1()
  RETURNS CHAR VARYING(25) 
AS $$
DECLARE
  local_char_var CHAR(30);
BEGIN
  SELECT datname
    INTO local_char_var
    FROM pg_database
    LIMIT 1;

  RETURN local_char_var;
END;
$$ LANGUAGE plpgsql;

Ok. Now we'll go through it line by line:

1. CREATE OR REPLACE FUNCTION func_1() - This line creates and names the function. The "OR REPLACE" will let us modify the function without fir DROPping it. func_1 can be any valid PLpgSQL name. Even if you are not declaring parameters, you must include the parenthesis ().
2. RETURNS CHAR VARYING(25) - RETURNS is the keyword that signifies what data type the function will be returing. RETURN is the equivalent in PL/SQL. Notice the S. CHAR VARYING is the equivalent of a VARCHAR2 in PL/SQL. In this case it will be a VARCHAR2(25).
3. AS $$ - AS is the same as the PL/SQL AS. The $$ is the function code delimiter. You can actually use single quotes here instead of $$ but in that case you would need to double all the quotes in your code. If that doesn't make sense, just consider the $$ mandatory. PLpgSQL is a more basic language than PL/SQL. The code is stored as text and compiled as it is run. The entire function body is just a string and the $$ is the delimiter.
4. DECLARE - used to begin the variable declaration area. If you are not declaring local variables, this is an optional keyword.
5. local_char_var CHAR(30); - A locally declared variable.
6. BEGIN - Begins the body of the the function.
7. SELECT datname INTO local_char_var FROM pg_database LIMIT 1; - This select statement is selecting the database name from the PG_DATABASE data dictionary table. The database name is being stored in the local variable local_char_var. The query is limiting the result set to 1 row.
8. RETURN local_char_var; - This line returns the local variable back to the calling program.
9. END; - Ends the body of the function.
10. $$ LANGUAGE plpgsql; - The $$ is the end delimiter of the function body (think of it as ending the string). The LANGUAGE plpgsql identifies the language type to the postgres engine so that it knows which language to run the program.

That's it for a very basic function. I'll build on this in the future. LewisC

postgresql function plpgsql programming