postgresql - Errors with an easy PL/pgSQL Function -

i'm trying write first pl/pgsql function. right supposed return number of characters in value passed it.

create or replace function public.cents(money)  returns int  language plpgsql  leakproof $function$ declare     new_price money;     size int; begin     size := char_length(money);     return size; end; $function$; 

when try test $66.66 1 error:

select cents($66.66); error:  syntax error @ or near ".66" line 1: select cents($66.66);                         ^ 

and if use $66 different error:

select cents($66); error:  there no parameter $66 line 1: select cents($66);                      ^ 

using integer 66 gives me third error:

select cents(66);        ^  hint:  no function matches given name , argument types. might need add explicit type casts. 

what doing wrong here?

are sure want use data type money? consider:

• postgresql: datatype should used currency?

if need type money, sure understand role of locale settings type. read manual.

you can't enter literals without single quotes (like numeric constants) - unless cast them, inefficient. that's not error. function work this:

create or replace function public.cents(_my_money_parameter money)   returns int $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 efficient, depends on locale settings. dot in example interpreted thousands separator , ignored (not decimal point) in locales.

notes

• you treat money parameter name in function body, it's data type. if want use parameter names, have declare them demonstrated. or refer parameters positional references: $1, $2 etc.

• char_length() expects character data type, cannot use data type money without casting. length() equivalent.

• if include dollar sign, need single quotes string literal: '$66.66' - , format must match locale setting work money.
  if supply numeric constant 66, postgres won't find function money parameter due rules of function type resolution. details:

  • is there way disable function overloading in postgres

start reading chapter constants in manual.
continue page on create function.