If you’re writing functions in postgres then you’ll probably be using a language such as plpgsql. Let’s say you’re writing a script to to add all of these functions to a new database, but you don’t know whether that language has been created yet. You’ll probably want to do something like CREATE LANGUAGE IF NOT [...]
Posts Tagged ‘query’
CREATE LANGUAGE if it doesn’t exist in PostgreSQL
Posted: 27th August 2011 by Tim in PostgreSQLTags: create, create language, exist, if exists, if not exists, language, plpgsql, postgres, PostgreSQL, query, script, sql, statement
1
Conditional COUNT in SQL
Posted: 10th October 2010 by Tim in SQLTags: count, COUNTIF, if, mssql, MySQL, oracle, PostgreSQL, query, sql, sql server, statement, sum
Sometimes you want to count the number of results which meet a certain condition. Excel has a COUNTIF(…) function but there is nothing like that in the (current) SQL standard. The solution is to mix the SUM function with a CASE statement. Confused? Let’s see an example. Imagine you have two tables – student (student_id, [...]
Inline CASE statement in PostgreSQL
Posted: 9th June 2010 by Tim in PostgreSQLTags: case, database, if, PostgreSQL, query, select, server, sql, statement, switch
PostgreSQL, unfortunately, does not provide an inline IF statement like some other SQL servers. CASE statements, however, can be run inline which can be quite handy. Let’s say you have a user table with a num_heads field. You want to know if the user is a zombie, human or alien with one query. This could [...]
PQexecParams example – PostgreSQL query execution with parameters
Posted: 19th November 2009 by Tim in C, PostgreSQLTags: C, coding, exec, parameter, PostgreSQL, programming, query
The PostgreSQL documentation states that PQexecParams can be called like so: PGresult *PQexecParams(PGconn *conn, const char *command, int nParams, const Oid *paramTypes, const char * const *paramValues, const int *paramLengths, const int *paramFormats, int resultFormat); There are two parts of this call which can be tricky to novice C and/or PostgreSQL users and aren’t explained [...]
Inline IF and CASE statements in MySQL
Posted: 13th August 2009 by Tim in MySQLTags: case, data, inline, MySQL, query, statement, switch
There are times where running IF statements inside a query can be useful. MySQL provides a simple way to do this through the use of IF and CASE statements. The IF statement takes three arguments; the conditional, the true value and the false value. False and true values may be static values or column values. [...]