Errors with an easy PL/pgSQL Function

Question

I'm trying to write my first PL/pgSQL function. For right now it is simply supposed to return the number of characters in a value that is passed to it.

CREATE OR REPLACE FUNCTION public.cents(money)
 RETURNS int
 LANGUAGE plpgsql
 LEAKPROOF
AS $function$
DECLARE
    new_price money;
    size int;
BEGIN
    size := char_length(money);
    RETURN size;
END;
$function$;

When I try to test with $66.66 I get one error:

select cents($66.66);
ERROR:  syntax error at or near ".66"
LINE 1: select cents($66.66);
                        ^

And if I use $66 I get a different error:

select cents($66);
ERROR:  there is no parameter $66
LINE 1: select cents($66);
                     ^

Using just the integer 66 gives me a third error:

select cents(66);
       ^

HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

What am I doing wrong here?

score 2 · Accepted Answer · edited May 23 '17 at 10:34

Are you sure you want to use the data type money? Consider:

PostgreSQL: Which Datatype should be used for Currency?

If you need the type money, be sure to understand the role of locale settings for this type. Read the manual.

You can't enter literals without single quotes (like numeric constants) - unless you cast them, which would be inefficient. But that's not the only error. Your function would work like this:

CREATE OR REPLACE FUNCTION public.cents(_my_money_parameter money)
  RETURNS int AS
$func$
BEGIN
   RETURN char_length(_my_money_parameter::text);
END
$func$  LANGUAGE plpgsql LEAKPROOF;

Call:

SELECT public.cents(money '66.66');
SELECT public.cents('66.66'::money);
SELECT public.cents(66.66::money);

The 1st call variant is the most efficient, but it depends on locale settings. The dot in my example is interpreted as thousands separator and ignored (not as decimal point) in some locales.

Notes

You treat money like a parameter name in the function body, but it's just the data type. If you want to use parameter names, you have to declare them like demonstrated. Or refer to parameters with positional references: $1, $2 etc.
char_length() expects a character data type, you cannot use it for data type money without casting. Just length() is equivalent.
If you include the dollar sign, you need single quotes for the string literal: '$66.66' - and the format must match your locale setting to work for money.
If you just supply the numeric constant 66, Postgres won't find the function with a money parameter due to the rules of function type resolution. Details:
- Is there a way to disable function overloading in Postgres

Start by reading the chapter Constants in the manual.
Continue with the page on CREATE FUNCTION.

score 1 · Answer 2 · edited Jun 02 '16 at 02:17

1

You need to put single-quotes around your input:

SELECT cents('$66.66');

If your settings don't allow this (still throw an error) you can try casting: SELECT cents('66.66'::float8::numeric::money);

Be sure to reference the docs as they provide a good overview: https://www.postgresql.org/docs/current/static/datatype-money.html

edited Jun 02 '16 at 02:17

Erwin Brandstetter

605,456
145
1,078
1,228

answered Jun 02 '16 at 01:02

Nick

7,103
2
21
43

1

You copied the cast from the manual: `::float8::numeric::money` but that's nonsense in this context. It's meant to convert numeric *values* and does not make sense for literal input. Aside from that, the answer misses most of the problems in the question and neglects to warn about possible caveats with the input `'$66.66'`. Your link to the manual is helpful, though, so I'll remove the downvote. – Erwin Brandstetter Jun 02 '16 at 02:16

Errors with an easy PL/pgSQL Function

2 Answers2

Notes