3

When I run this script:

CREATE OR REPLACE FUNCTION registeruser(character varying, character varying, money, character varying, character varying, date)
  RETURNS void AS
$BODY$
BEGIN
INSERT INTO "Users"("Name", "Surnames", "Money", "Email", "Password", "UserType", "State", "BirthDate")
VALUES ($1,$2,$3,$4,$5,'Costumer','Active',$6);

END;
$BODY$  LANGUAGE plpgsql VOLATILE;

SELECT RegisterUser('Andrea','Gianchero',0,'gian@hotmail.com','rome','1960-04-12');

I get this error:

ERROR:  function registeruser(unknown, unknown, integer, unknown, unknown, unknown) does not exist
LINE 1: SELECT RegisterUser('Andrea','Gianchero',0,'gian@hotmail.com...
             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

How to fix this?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Francisco
  • 41
  • 1
  • 1
  • 4

1 Answers1

3

A string literal such as 'Andrea' is untyped and is not the same as a parameter of type varchar. Test the following two lines in your psql console:

select 'Andrea';
select 'Andrea'::character varying;

The first will output Andrea with default column name ?column? the second will output Andrea but with column name varchar. From the documentation:

The notations varchar(n) and char(n) are aliases for character varying(n) and character(n), respectively.

To solve your problem, simply cast all the string literals in your function call to type varchar (or character varying). Also, the 0 should be cast to type money, at the moment it is recognized as an integer:

SELECT RegisterUser('Andrea'::varchar, 'Gianchero'::varchar, 0::money,...)
apotry
  • 1,856
  • 2
  • 17
  • 23
  • I already do but it dosen't solve the problem ERROR: function registeruser(unknown, unknown, money, unknown, unknown, unknown, unknown, unknown) does not exist LINE 1: SELECT registeruser('Alfonso','Gianchero',0::money,'gian@gma... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. – Francisco Feb 03 '16 at 22:22
  • @Francisco: did you also cast the integer value to `money` as nick barnes wrote in his comment? –  Feb 04 '16 at 07:00
  • @Francisco: The error message in your comment indicates *8* parameters while your function has *6*. Remove the trailing parameters and it should work. This is a matter of [Function Type Resolution](http://stackoverflow.com/a/34324169/939860). – Erwin Brandstetter Feb 04 '16 at 16:03
  • `?column?` is not a data type, but the default column name. Unless there is function overloading, only `money` requires a cast, since there is no implicit cast defined. It would be better to cast the string literal directly: `money '0'` – Erwin Brandstetter Feb 04 '16 at 16:08