StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Update</strong>, fleshing out terrific suggestion by @Tometzky.</p> <p>This ought to give you a MySQL <code>FIELD()</code>-alike function under pg 8.4:</p> <pre><code>-- SELECT FIELD(varnames, 'foo', 'bar', 'baz') CREATE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS numeric AS $$ SELECT COALESCE( ( SELECT i FROM generate_subscripts($2, 1) gs(i) WHERE $2[i] = $1 ), 0); $$ LANGUAGE SQL STABLE </code></pre> <p><em>Mea culpa</em>, but I cannot verify the above on 8.4 right now; however, I can work backwards to a "morally" equivalent version that works on the 8.1 instance in front of me:</p> <pre><code>-- SELECT FIELD(varname, ARRAY['foo', 'bar', 'baz']) CREATE OR REPLACE FUNCTION field(anyelement, anyarray) RETURNS numeric AS $$ SELECT COALESCE((SELECT i FROM generate_series(1, array_upper($2, 1)) gs(i) WHERE $2[i] = $1), 0); $$ LANGUAGE SQL STABLE </code></pre> <p>More awkwardly, you still can portably use a (possibly derived) table of currency code rankings, like so:</p> <pre><code>pg=> select cc.* from currency_codes cc left join (select 'GBP' as code, 0 as rank union all select 'EUR', 1 union all select 'BBD', 2 union all select 'AUD', 3 union all select 'CAD', 4 union all select 'USD', 5) cc_weights on cc.code = cc_weights.code order by rank desc, name asc; code | name ------+--------------------------- USD | USA bits CAD | Canadian maple tokens AUD | Australian diwallarangoos BBD | Barbadian tridents EUR | Euro chits GBP | British haypennies (6 rows) </code></pre>

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