PostgreSQL, function use?

Question

I have function like this :

CREATE OR REPLACE FUNCTION f_insert_account(usernames character varying, passwds  
character varying, identitass integer, jeniss smallint, statuss smallint, cnames 
character varying, unames character varying)
RETURNS character varying AS
$BODY$
DECLARE
iduserx int4;
usernamex varchar;
er int2:=0;
pesan varchar:='';
BEGIN
insert into t_account(username,passwd,identitas,jenis,status,cname,uname)
values(usernamex,f_encr(passwds),identitass,jeniss,statuss,cnames,unames);
RETURN pesan;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION f_insert_account(usernames character varying, passwds character 
varying, identitass integer, jeniss smallint, statuss smallint, cnames character 
varying, unames character varying) OWNER TO postgres;

GRANT EXECUTE ON FUNCTION f_insert_account(usernames character varying, passwds 
character varying, identitass integer, jeniss smallint, statuss smallint, cnames  
character varying, unames character varying) TO public;

GRANT EXECUTE ON FUNCTION f_insert_account(usernames character varying, passwds 
character varying, identitass integer, jeniss smallint, statuss smallint, cnames   
character varying, unames character varying) TO postgres;

then I use that like this :

select f_insert_account('dayat', 'dayat', 1, 1, 1, 'cname', 'uname')

Error like this always appear : ERROR: function f_insert_account("unknown", "unknown", integer, integer, integer, "unknown", "unknown") does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You may need to add explicit type casts. Character: 8

why is that ???

thanx,

Dayat

You show the creation of f_insert_account() and then complain about errors with f_account_sp(). Please clarify. — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 17, 2011 at 6:28

Mike T · Accepted Answer · 2011-11-17 07:50:35Z

10

The smallint datatype is a bit of a gotcha, since you need to cast these:

select f_insert_account('dayat', 'dayat', 1, 1::smallint, 1::smallint, 'cname', 'uname')

If you want to avoid this, I suggest changing your function definition to use plain old integer types.

answered Nov 17, 2011 at 7:50

Mike T

44.3k18 gold badges166 silver badges213 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL, function use?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related