I have a function in PostgreSQL 9.1 called fun_test
. It has a composite type as input parameter and I keep getting a casting error when I call it.
CREATE OR REPLACE FUNCTION netcen.fun_test(myobj netcen.testobj)
RETURNS boolean AS
$BODY$
DECLARE
tmp_code smallint;
cur_member refcursor;
BEGIN
-- Check if the member exists first
OPEN cur_member FOR
EXECUTE 'SELECT testkey FROM netcen.test WHERE testkey=' || myobj.testkey ;
FETCH cur_member INTO tmp_code;
CLOSE cur_member;
CASE tmp_code
WHEN COALESCE(tmp_code,0)=0 THEN
-- Record not found INSERT a new record
-- will skip user defined validation for now
insert into netcen.test values(myobj.testkey,
myobj.tes,
myobj.testname);
ELSE
-- Record found UPDATE the record
update netcen.test set
test=myobj.test,
testname=myobj.testname WHERE testkey=myobj.testkey;
END CASE;
END;$BODY$
LANGUAGE plpgsql;
Below is the type testobj
CREATE TYPE netcen.testobj AS
(testkey smallint,
tes text,
testname text);
When I call the function:
SELECT netcen.fun_test('(3,khaendra@me.com,khaendra)':: netcen.testobj);
.. I get the following error message:
ERROR: operator does not exist: smallint = boolean
LINE 1: SELECT "__Case__Variable_8__" IN (COALESCE(tmp_code,0)=0)
^
HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.
QUERY: SELECT "__Case__Variable_8__" IN (COALESCE(tmp_code,0)=0)
CONTEXT: PL/pgSQL function "fun_test" line 11 at CASE
Where should I cast?
Definition of the table netcen.test
:
CREATE TABLE netcen.test (
testkey smallint NOT NULL DEFAULT 0,
tes netcen.dom_email_validation,
testname text,
CONSTRAINT key PRIMARY KEY (testkey)
)
@ Erwin, thanks for the links. I read and have modified my function to this, please go through it and tell me if it can work well with several clients calling the same function concurrently?
CREATE OR REPLACE FUNCTION netcen.fun_test_modified(myobj netcen.test)
RETURNS boolean AS
$BODY$
DECLARE
myoutput boolean :=false;
BEGIN
update netcen.test set
tes=myobj.tes,
testname=myobj.testname WHERE testkey=myobj.testkey;
IF FOUND THEN
myoutput:= TRUE;
RETURN myoutput;
END IF;
BEGIN
INSERT INTO netcen.test values(myobj.testkey,
myobj.tes,
myobj.testname);
myoutput:= TRUE;
EXCEPTION WHEN OTHERS THEN
update netcen.test set
tes=myobj.tes,
testname=myobj.testname WHERE testkey=myobj.testkey;
myoutput:= TRUE;
END;
RETURN myoutput;
END;
$BODY$ LANGUAGE plpgsql;
I have done away with the type test and just used the table test
! I didn't know that could work!
Best Answer
Answer
The error occurs here:
Works like this:
You mixed two different syntax variants of PL/pgSQL
CASE
("simple" vs. "searched") in an incompatible way.There is another error:
Did you mean:
There is also no need for
CREATE TYPE netcen.testobj ...
. You can use the table namenetcen.test
as type name.You really want to "UPSERT"
In Postgres 9.5 or later use the new UPSERT (
INSERT ... ON CONFLICT DO UPDATE
). Like:For older versions you can emulate an
UPSERT
. The simple form in plpgsql, without concurrency:Could just be plain SQL with a data-modifying CTE:
The time window for a possible race condition is extremely tiny with this form (single combined statement). If concurrency is still an issue (heavy concurrent write load), then ...
Your function reviewed
If the function returns, the row has been inserted or updated. The only other way is an
EXCEPTION
of a different kind. The returned valuetrue
is just noise. (Might be more interesting to returntrue
forINSERT
andfalse
forUPDATE
.) So I simplified:Related answers on SO:
Blog post on UPSERT by Depesz.