SQL
Analyzing Queries in Postgres
Yesterday I was curious if there was a good way to write my IDS query using a single 'select'. All of us had the right idea of using a sub-query to limit the results from the join, although there were different ideas about how to use a sub-query. Richard's solution was the most succinct. However, there is an important follow-up lesson that I want to talk about. What I asked yesterday is representative of something we all seem to ask ourselves consistently: "What is the most clever way I can do this?" When it comes to database queries I believe we have a tendency to see how much we can accomplish in one swoop. At the heart of this tendency is the assumption that more queries equals less efficiency. Please strive to constantly challenge this assertion. Our thoughts about software should be continuously reinforced by demonstrable ...
0 Comment | Posted in PostgreSQL,SQL,blog
January 22, 2010
SQL Movement Commands
Most programming modes in Emacs support two commands, beginning-of-defun and end-of-defun. These move you to the beginning and end of the nearest function, and by default are bound to C-M-a and C-M-e, respectively. However, sql-mode does not have anything similar. I have the above two commands bound to C-up and C-down for quickly moving around functions, but I find myself often hitting them when viewing an SQL file to move through tables---which doesn't work. Fortunately that functionality is pretty easy to add. For my fellow Emacs users, here is some code I whipped up that you can use to easily move between tables and views. (defconst sql-beginning-of-table-or-view-regexp "^\\s-*create\\s-+\\(?:table\\|view\\)" "Regular expression for matching the start of an SQL table or view definition.") (defun sql-prev-table-or-view () "Move backwards to the beginning of the nearest table or view from point." (interactive) (re-search-backward sql-beginning-of-table-or-view-regexp nil 'noerror)) (defun sql-next-table-or-view () "Move forwards to the beginning of the nearest table or view from point." (interactive) (re-search-forward sql-beginning-of-table-or-view-regexp nil 'noerror)) Then you ...
PostgreSQL Stored Procedures – Part 2
In a follow up to our initial Stored Procedures article, here we present some more complex examples based on real-world work we've completed on a PostgreSQL 8.3 database. Fetching A Newly Inserted ID Often times you will want to insert a record into a table that has a default ID string. There is any easy method for doing this with the INSERT...RETURNING syntax. In this stored procedure example below we add a new group name to our groups table and get back the auto-generated ID (primary key) in the newid variable. We then use that to insert a record in our group members table AND return the newly created ID back from the stored procedure, making it easy for the calling program to catch the new ID: DECLARE newid BIGINT; BEGIN INSERT INTO groups (group_name) values ($1) RETURNING group_id INTO newid; INSERT INTO group_members (group_id, ...
PostgreSQL Stored Procedures Part 1
Stored procedures are a great way to enforce business and data access logic in a complex environment. Often with larger projects you will have multiple applications architected by a varied set of professionals all interacting with the same data sets. Stored procedures are a tried and true way to regain some control over those data sets and ensure standards compliance. The following paragraphs will show the basic syntax and some simple examples for using stored procedures in PostgreSQL. Syntax CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [ RETURNS rettype ] { LANGUAGE langname | IMMUTABLE | STABLE | VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | COST execution_cost | ROWS result_rows | SET configuration_parameter { TO value | = value | FROM ...
0 Comment | Posted in Database,PostgreSQL,SQL,blog
November 10, 2009
On Delete Cascade
ON DELETE CASCADE In a relational database where would you use the following construct? What does it do? … ON DELETE CASCADE Answer ON DELETE CASCADE is used when creating a table that has a field with a FOREIGN KEY. The purpose it to ensure that when a row in the parent table is deleted all of the related rows in the CHILD table are deleted. Example CREATE TABLE cities ( city varchar(80) primary key ); CREATE TABLE weather_history ( city varchar(80) references cities(city) ON DELETE CASCADE, temp_lo int, temp_hi int, date date ); The parent table is cities, the child is weather_history. We ...
