Source: SQL Joins Visualizer
I’m using this perfect tool called Sublime Text 3 for a bunch of tasks. One of them is viewing SQL scripts from time to time. ST3 has perfect SQL highlighting, but what I miss the most is the context help functionality, e.g. I select “LEFT JOIN” and hit F1 hot key. But that’s not problem since ST3 has a lot of packages. To solve my problem I need GotoDocumentation package and some tuning. Here is my settings for GotoDocumentation:
// obj containing the docs for each scope
// these are merged with the default ones
// the key value pair represent scope -> doc url
// supported placeholders:
// - %(query)s the selected text/word
// - %(scope)s the current scope
// if we have no docs for the current scope
// we will try using the fallback one,
// to disable set to false
Task: get title (or excerpt) from given text (body)
left(body, 29) ||
COALESCE( substring( substr( body, 30 ) from '(.+?)(\,| )' ), '' ) as title,
Details: Take the first 29 characters, concatenate them with substring started from the 30-th character till the first occurrence of comma or space. Or concatenate with empty string, if pattern doesn’t match (COALESCE).
- MERGE INTO acme_obj_value d
- USING ( SELECT object_id
- FROM acme_state_tmp
- ) s
- ON (d.object_id = s.object_id)
- WHEN matched THEN
- UPDATE SET d.date_value = LEAST(l_dt, d.date_value)
- WHEN NOT matched THEN
- INSERT (d.id, d.object_id, d.date_value)
- VALUES (acme_param_sequence.NEXTVAL, s.object_id, l_dt)
- WITH s AS (
- SELECT object_id
- FROM acme_state_tmp
- upd AS (
- UPDATE acme_obj_value
- SET date_value = LEAST(l_dt, d.date_value)
- FROM s
- WHERE acme_obj_value.object_id = s.object_id
- RETURNING acme_obj_value.object_id
- INSERT INTO acme_obj_value(id, object_id, date_value)
- SELECT NEXTVAL('acme_param_sequence'), s.object_id, l_dt
- FROM s
- WHERE s.object_id NOT IN (SELECT object_id FROM upd)
Want to force your dev team to address Compiler Warnings rather than ignoring them?
Elevate Warnings to Errors:
1) Go to Project Options
2) Under Delphi Compiler, select Hints and Warnings
3) Expand the Output warnings item
4) For each important Warning you wish for your developers to address rather than ignore, select the drop-down list and select Error.
As you probably know PostgreSQL sources are managed by Git version control system. Which is great, but for me as SVN user it would be much better to get access to the sources using familiar tools, e.g. TortoiseSVN. Mainly because I don’t need write access, read mode only. There is a workaround for such guys.
So in my case I need only trunk with the latest commits. I’ve created an empty folder and made Checkout to the https://github.com/postgres/postgres/trunk URL.
I was playing with some unit tests for PostgresDAC recently. And one test case was to check TPSQLDataset.Locate routine which allows partial-string and case-sensitive matching options. So I want to have some test result set with integer and text columns. The simplest for me are numbers in both representations: using digits and using words, e.g.
Since I hadn’t much spare time I used nice VALUES command:
VALUES (1, 'one'), (2, 'two'), (3, 'three');
Considering I needed special names for columns I used more complex query:
SELECT col1, col2::varchar(10) FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t(col1, col2);
I did my task well. However I was wondering is there any built-in function in PostgreSQL which may convert integer to such words representation. The answer is: not exactly.
I found some references to cash_words function. But there is no any information (except entry in 7.3 release notes) in the official documents. The use case is quite simple:
SELECT cash_words(gen.i::money) FROM generate_series(1, 10) AS gen(i);
I’m fine with this result set, who needs only numbers may trim values using standard functions.
PS In case someone wants to create his own implementation using C (or plpgsql, who knows), cash_words function may be found in the \src\backend\utils\adt\cash.c file. It uses num_word private routine.