Convert numeric to string inside a user-defined function

Question

I am trying to call/convert a numeric variable into string inside a user-defined function. I was thinking about using to_char, but it didn't pass.

My function is like this:

create or replace function ntile_loop(x numeric)
returns setof numeric as 
$$
  select

  max("billed") as _____(to_char($1,'99')||"%"???) from 
      (select "billed", "id","cm",ntile(100) 
      over (partition by "id","cm" order by "billed")
      as "percentile" from "table_all") where "percentile"=$1
      group by "id","cm","percentile";
$$
language sql;

My purpose is to define a new variable "x%" as its name, with x varying as the function input. In context, x is numeric and will be called again later in the function as a numeric (this part of code wasn't included in the sample above).

What I want to return:

I simply want to return a block of code so that every time I change the percentile number, I don't have to run this block of code again and again. 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.

That's why I was thinking about macro or function, but didn't find any solutions I like.

Thank you for your answers. Yes, I will definitely read basics while I am learning. Today's my second day to use SQL, but have to generate some results immediately.

Answer 1

Converting numeric to text is the least of your problems.

My purpose is to define a new variable "x%" as its name, with x varying as the function input.

• First of all: there are no variables in an SQL function. SQL functions are just wrappers for valid SQL statements. Input and output parameters can be named, but names are static, not dynamic.

• You may be thinking of a PL/pgSQL function, where you have procedural elements including variables. Parameter names are still static, though. There are no dynamic variable names in plpgsql. You can execute dynamic SQL with EXECUTE but that's something different entirely.

• While it is possible to declare a static variable with a name like "123%" 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.

• Since the window function ntile() returns integer and you run an equality check on the result, the input parameter should be integer, not numeric.

• To assign a variable in plpgsql you can use the assignment operator := for a single variable or SELECT INTO for any number of variables. Either way, you want the query to return a single row or you have to loop.

• If you want the maximum billed from the chosen percentile, you don't GROUP BY x, y. That might return multiple rows and does not do what you seem to want. Use plain max(billed) without GROUP BY to get a single row.

• You don't need to double quote perfectly legal column names.

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.

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;

Long story short, you need to study the basics before you try to create sophisticated functions.

Plain SQL

After Q update:

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.

This simple query should do it all:

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;

% .. modulo operator
GROUP BY 1,2,3 .. positional parameter

Answer 2

It looks like you're looking for return query execute, returning the result from a dynamic SQL statement:

http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html

http://www.postgresql.org/docs/current/static/plpgsql-statements.html