StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Don't use <a href="http://www.postgresql.org/docs/current/static/sql-createtype.html">CREATE TYPE</a> to return a polymorphic result. Use and abuse the <a href="http://www.postgresql.org/docs/9.0/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS">RECORD type</a> instead. Check it out:</p> <pre><code>CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$ DECLARE ret RECORD; BEGIN -- Arbitrary expression to change the first parameter IF LENGTH(a) < LENGTH(b) THEN SELECT TRUE, a || b, 'a shorter than b' INTO ret; ELSE SELECT FALSE, b || a INTO ret; END IF; RETURN ret; END;$$ LANGUAGE plpgsql; </code></pre> <p>Pay attention to the fact that it can optionally return <strong>two</strong> or <strong>three</strong> columns depending on the input.</p> <pre><code>test=> SELECT test_ret('foo','barbaz'); test_ret ---------------------------------- (t,foobarbaz,"a shorter than b") (1 row) test=> SELECT test_ret('barbaz','foo'); test_ret ---------------------------------- (f,foobarbaz) (1 row) </code></pre> <p>This does wreak havoc on code, so do use a consistent number of columns, but it's ridiculously handy for returning optional error messages with the first parameter returning the success of the operation. Rewritten using a consistent number of columns:</p> <pre><code>CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$ DECLARE ret RECORD; BEGIN -- Note the CASTING being done for the 2nd and 3rd elements of the RECORD IF LENGTH(a) < LENGTH(b) THEN ret := (TRUE, (a || b)::TEXT, 'a shorter than b'::TEXT); ELSE ret := (FALSE, (b || a)::TEXT, NULL::TEXT); END IF; RETURN ret; END;$$ LANGUAGE plpgsql; </code></pre> <p>Almost to epic hotness:</p> <pre><code>test=> SELECT test_ret('foobar','bar'); test_ret ---------------- (f,barfoobar,) (1 row) test=> SELECT test_ret('foo','barbaz'); test_ret ---------------------------------- (t,foobarbaz,"a shorter than b") (1 row) </code></pre> <p>But how do you split that out in to multiple rows so that your ORM layer of choice can convert the values in to your language of choice's native data types? The hotness:</p> <pre><code>test=> SELECT a, b, c FROM test_ret('foo','barbaz') AS (a BOOL, b TEXT, c TEXT); a | b | c ---+-----------+------------------ t | foobarbaz | a shorter than b (1 row) test=> SELECT a, b, c FROM test_ret('foobar','bar') AS (a BOOL, b TEXT, c TEXT); a | b | c ---+-----------+--- f | barfoobar | (1 row) </code></pre> <p>This is one of the coolest and most underused features in PostgreSQL. Please spread the word.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload