LLC | PostgreSQL General Bits Newsletter. This is PostgreSQL General Bits issue number 20!
There have been 120 items covering about that many threads in pgsql-general. Those threads contained about 642 messages. But those numbers do not count all of the messages read, tests run and research done. I'd like to thank the active contributors to pgsql-general as well as the question askers for providing interesting and useful topics and information to write about. I'd also like to thank the people who have given me excellent feedback and suggestions.
Creating Functions. There are several ways to create function with PL/pgSQL.
You can use the editor of your choice to develop the code for the function. Once you have written the code, you can load the file with psql . Quick Guide to writing PLPGSQL Functions: Part 3 - NOTICES, RECU. In this third part of our PLPGSQL Quick Guide series, we shall delve into writing recursive functions.
Before we do that, we shall demonstrate a very important but trivial feature in PostgreSQL and that is the RAISE NOTICE feature. There are more elegant ways of debugging, but this is the simple brain dead way of doing so. RAISE Notices in plpgsql are generally used for two reasons: As a simple debugging tool to output state variables in a function call.As a WARNING to a user to inform them of important things such as this function is deprecated and should not be used or they are using something in an incorrect way. How to check, if a value is an integer with plpgsql? - Stack Ove.
Checking for the existence of a column in a table « Systems Engi. Posted by decipherinfosys on August 30, 2007 As DBAs or database developers, when we need to write up scripts that need to be rolled out from the development environment to QA or production environment, it is always a good idea to have non-destructive DDL scripts in place.
One such scenario is an addition of a column to an existing table. We want to add a new column but first we want to make sure that the column does not exist in the table rather than getting an error at the time of running the script. In this blog post, we will show you how can we check for the existence of a column before adding it to the table using a small code snippet for Oracle and SQL Server. Let us first create a table. How to optimize PostgreSQL database size. PostgreSQL is a powerful, open source relational database system.
It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. One of the PostgreSQL's most sophisticated features is so called Multi-Version Concurrency Control (MVCC), a standard technique for avoiding conflicts between reads and writes of the same object in database. MVCC guarantees that each transaction sees a consistent view of the database by reading non-current data for objects modified by concurrent transactions.
Thanks to MVCC, PostgreSQL has great scalability, a robust hot backup tool and many other nice features comparable to the most advanced commercial databases.
PostgreSQL 8.3 PLPGSQL Cheatsheet. MySQL vs PostgreSQL. MySQL vs PostgreSQL is a decision many must make when approaching open-source relational database management systems.
Both are time-proven solutions that compete strongly with proprietary database software. MySQL has long been assumed to be the faster but less full-featured of the two database systems, while PostgreSQL was assumed to be a more densely featured database system often described as an open-source version of Oracle.