I have the table with some columns:
--table
create table testz
(
ID uuid,
name text
);
Note: I want to insert ID
values by passing as a parameter to the function. Because I am generating the ID
value
in the front end by using uuid_generate_v4()
. So I need to pass the generated value to the function to insert
into the table
--function
CREATE OR REPLACE FUNCTION testz
(
p_id varchar(50),
p_name text
)
RETURNS VOID AS
$BODY$
BEGIN
INSERT INTO testz values(p_id,p_name);
END;
$BODY$
LANGUAGE PLPGSQL;
--EXECUTE FUNCTION
SELECT testz('24f9aa53-e15c-4813-8ec3-ede1495e05f1','Abc');
Getting an error:
ERROR: column "id" is of type uuid but expression is of type character varying
LINE 1: INSERT INTO testz values(p_id,p_name)
You need a simple cast to make sure PostgreSQL understands, what you want to insert:
INSERT INTO testz values(p_id::uuid, p_name); -- or: CAST(p_id AS uuid)
Or (preferably) you need a function, with exact parameter types, like:
CREATE OR REPLACE FUNCTION testz(p_id uuid, p_name text)
RETURNS VOID AS
$BODY$
BEGIN
INSERT INTO testz values(p_id, p_name);
END;
$BODY$
LANGUAGE PLPGSQL;
With this, a cast may be needed at the calling side (but PostgreSQL usually do better automatic casts with function arguments than inside INSERT
statements).
If your function is that simple, you can use SQL
functions too:
CREATE OR REPLACE FUNCTION testz(uuid, text) RETURNS VOID
LANGUAGE SQL AS 'INSERT INTO testz values($1, $2)';