Issue with composite type in an UPSERT function

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 functio

Answer

The error occurs here:

CASE tmp_code
    WHEN COALESCE(tmp_code,0)=0 THEN 

Works like this:

CASE WHEN COALESCE(tmp_code,0)=0 THEN

You mixed two different syntax variants of PL/pgSQL CASE ("simple" vs. "searched") in an incompatible way.

There is another error:

update netcen.test set 
test=myobj.test,
testname=myobj.testname  WHERE testkey=myobj.testkey;

Did you mean:

UPDATE test
SET    tes = myobj.tes
     , testname = myobj.testname
WHERE  testkey = myobj.testkey;

There is also no need for CREATE TYPE netcen.testobj .... You can use the table name netcen.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:

• UPSERT with ON CONFLICT using values from source table in the UPDATE part

For older versions you can emulate an UPSERT. The simple form in plpgsql, without concurrency:

CREATE OR REPLACE FUNCTION fun_test(myobj testobj)
  RETURNS boolean
  LANGUAGE plpgsql AS
$func$
BEGIN    
   UPDATE test
   SET    tes = myobj.tes
        , testname = myobj.testname
   WHERE  testkey = myobj.testkey;

   IF FOUND THEN
      RETURN FALSE;
   ELSE
      INSERT INTO test SELECT (myobj).*;
      RETURN TRUE;
   END IF;
END
$func$;

Could just be plain SQL with a data-modifying CTE:

WITH my_row(testkey, tes, testname) AS (
    SELECT 1::smallint, 'khaendra@me.net', 'khaendra'
    )
, u AS (
    UPDATE test t
    SET    tes = m.tes
         , testname = m.testname
    FROM   my_row m
    WHERE  t.testkey = m.testkey
    RETURNING t.testkey
    )
INSERT INTO test (testkey, tes, testname)
SELECT * FROM my_row
WHERE  NOT EXISTS (SELECT FROM u);

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 value true is just noise. (Might be more interesting to return true for INSERT and false for UPDATE.) So I simplified:

CREATE OR REPLACE FUNCTION netcen.fun_test_modified(myobj netcen.test)
  RETURNS boolean
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE netcen.test
   SET    tes = myobj.tes
        , testname = myobj.testname
   WHERE  testkey = myobj.testkey;

   IF FOUND THEN
      RETURN true;
   END IF;

   BEGIN
      INSERT INTO netcen.test 
      SELECT (myobj).*;  -- simpler form, parenthesis needed.

   EXCEPTION WHEN unique_violation THEN   -- cleaner
      UPDATE netcen.test
      SET    tes = myobj.tes
           , testname = myobj.testname
      WHERE  testkey = myobj.testkey;
   END;
   RETURN true;
END
$func$;

Related answers on SO:

• Upsert with a transaction

• Is SELECT or INSERT in a function prone to race conditions?

Blog post on UPSERT by Depesz.

StackExchange Database Administrators Q#33319, answer score: 7