All About PostgreSQL

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.

Read the rest of this entry.

To call either a function, you can run:

SELECT func_1();

Or

SELECT * FROM func_1();

1. PostgreSQL vs Firebird, August 2007 (and hopefully it will remain updated)


2. PostgreSQL vs. SQL Server: PostgreSQL is right for the Microsoft stack, SearchEntepriseLinux May 15, 2006


3. PostgreSQL vs. Oracle: Users speak out, SeachOracle April 6, 2006


4. Oracle 10g vs PostgreSQL 8 vs MySQL 5, This one I wrote, Aug 22, 2005


5. MySQL vs. PostgreSQL, Aug 9, 2005


6. PostgreSQL vs MySQL with Rails, June 18, 2005


7. PostgreSQL or MySQL - Feb 15 2005, a fairly nice comparison actually.


8. PostgreSQL vs. MySQL vs. Commercial Databases: It's All About What You Need - DevX April 12, 2004


9. PostgreSQL vs MySQL: Which is better?, DatabaseJournal Dec 16, 2003


10. Firebird vs Postgres, Forum Post, April 23, 2002


11. PostgreSQL vs. MySQL (Web Techniques, Sep 2001), Dr Dobbs, Jan 1, 2002