StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Converting <code>numeric</code> to <code>text</code> is the least of your problems.</p> <blockquote> <p>My purpose is to define a new variable "x%" as its name, with x varying as the function input.</p> </blockquote> <ul> <li><p>First of all: <strong>there are no variables in an SQL function.</strong> SQL functions are just wrappers for valid SQL statements. Input and output parameters can be named, but names are <em>static</em>, not dynamic.</p></li> <li><p>You may be thinking of a <a href="http://www.postgresql.org/docs/current/interactive/plpgsql.html" rel="nofollow noreferrer"><strong>PL/pgSQL</strong> function</a>, where you have procedural elements including variables. Parameter names are still <strong>static</strong>, though. There are no dynamic variable names in plpgsql. You can execute dynamic SQL with <a href="http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" rel="nofollow noreferrer"><code>EXECUTE</code></a> but that's something different entirely.</p></li> <li><p>While it is possible to declare a <em>static</em> variable with a <strong><em>name</em></strong> like <code>"123%"</code> it is really exceptionally uncommon to do so. Maybe for deliberately obfuscating code? Other than that: Don't. Use proper, simple, legal, lower case variable names without the need to double-quote and without the potential to do something unexpected after a typo.</p></li> <li><p>Since the window function <a href="http://www.postgresql.org/docs/current/interactive/functions-window.html" rel="nofollow noreferrer"><code>ntile()</code></a> returns <code>integer</code> and you run an equality check on the result, the input parameter should be <strong><code>integer</code></strong>, not <code>numeric</code>.</p></li> <li><p>To <a href="http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT" rel="nofollow noreferrer">assign a variable in plpgsql</a> you can use the assignment operator <code>:=</code> for a single variable or <code>SELECT INTO</code> for any number of variables. Either way, you want the query to return <strong>a single row</strong> or you have to <a href="http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING" rel="nofollow noreferrer"><strong>loop</strong></a>.</p></li> <li><p>If you want the maximum <code>billed</code> from the chosen percentile, you don't <code>GROUP BY x, y</code>. That might return multiple rows and does not do what you seem to want. Use plain <code>max(billed)</code> without <code>GROUP BY</code> to get a single row.</p></li> <li><p>You don't need to double quote <a href="http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS" rel="nofollow noreferrer">perfectly legal column names</a>.</p></li> </ul> <p>A valid function might look like this. It's not exactly what you were trying to do, which cannot be done. But it may get you closer to what you actually need.</p> <p></p></p> <pre><code>CREATE OR REPLACE FUNCTION ntile_loop(x integer) RETURNS SETOF numeric as $func$ DECLARE myvar text; BEGIN SELECT INTO myvar max(billed) FROM ( SELECT billed, id, cm ,ntile(100) OVER (PARTITION BY id, cm ORDER BY billed) AS tile FROM table_all ) sub WHERE sub.tile = $1; -- do something with myvar, depending on the value of $1 ... END $func$ LANGUAGE plpgsql; </code></pre> <p>Long story short, you need to <strong>study the basics</strong> before you try to create sophisticated functions.</p> <h3>Plain SQL</h3> <p>After Q update:</p> <blockquote> <p>I'd like to calculate 5, 10, 20, 30, ....90th percentile and display all of them in the same table for each id+cm group.</p> </blockquote> <p>This simple query should do it all:</p> <pre><code>SELECT id, cm, tile, max(billed) AS max_billed FROM ( SELECT billed, id, cm ,ntile(100) OVER (PARTITION BY id, cm ORDER BY billed) AS tile FROM table_all ) sub WHERE (tile%10 = 0 OR tile = 5) AND tile <= 90 GROUP BY 1,2,3 ORDER BY 1,2,3; </code></pre> <p><a href="http://www.postgresql.org/docs/current/interactive/functions-math.html" rel="nofollow noreferrer"><code>%</code> .. modulo operator</a><br> <a href="https://stackoverflow.com/questions/15847173/concatenate-multiple-result-rows-of-one-column-into-one-group-by-another-column/15850510#15850510"><code>GROUP BY 1,2,3</code> .. positional parameter</a></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