Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
what could be the issue?

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 VOLATILE
  COST 100;

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 i 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 VOLATILE
  COST 100;

I have done away with the type test and just used the table test! thanks i didnt know it could work!

share|improve this question
1  
Please don't cross post the same question on stackoverflow and here. –  dezso Jan 22 '13 at 15:04

3 Answers 3

up vote 6 down vote accepted

Answer

The error occurs here:

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

Would have to be

CASE WHEN COALESCE(tmp_code,0)=0 THEN

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

Another error

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;

Also: There is no need for CREATE TYPE netcen.testobj ....
You can just use the table name netcen.test as type name.

What you really want

On closer inspection, this whole function is going the wrong way. What you seem to be trying to achieve is a classical UPSERT.

The simple form of what you are trying to do in plpgsql (if concurrency is not an issue):

CREATE OR REPLACE FUNCTION fun_test(myobj testobj)
  RETURNS boolean 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$ LANGUAGE plpgsql;

Could be done with plain SQL, too (if concurrency is not an issue):

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 1 FROM u);

If concurrency is an issue, read more about the classical UPSERT problem in this blog post by Depesz:

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 AS
$BODY$
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
$BODY$  LANGUAGE plpgsql;
share|improve this answer
    
what do you mean if concurrency is not an issue? –  indago Jan 22 '13 at 11:28
    
@indago: Concurrency is an issue in databases where multiple clients might try to INSERT / UPDATE at the same time. There is a very slim chance for a race condition. Read the article I link to or try the manual here. –  Erwin Brandstetter Jan 22 '13 at 11:33
    
@ Erwin, concurrency is an issue ofcourse because more than ten clients will be connected and working at the same time on this database,thats why i want all the inserts and updates to be handled in the database by functions like these. can i still use the functions you gave me up here? please give me a modified version that caters for concurrency. Thanks alot for your help! –  indago Jan 22 '13 at 11:38
    
@indago: I did. Follow the link at the end of my answer. –  Erwin Brandstetter Jan 22 '13 at 11:39
1  
@indago: Sure you can return text. Just do it. If you run into problems, post another question, not a comment here. –  Erwin Brandstetter Jan 22 '13 at 14:50

While klin is technically right in his answer about how to fix your current function, let me question your whole approach. What you try to achieve is called 'UPSERT' and with PostgreSQL 9.1 (which offers writable CTEs) you have a very simple way to achieve this. I omitted the function definition for the sake of clarity, but you can easily wrap it in a query language function (that is, ending in LANGUAGE sql):

WITH upd AS (
    UPDATE netcen.test 
    SET (test, testname) = ($1.test, $1.testname)
    WHERE testkey =  $1.testkey
    RETURNING *
)
INSERT INTO netcen.test (
    testkey,
    test, 
    testname
)
VALUES (
    $1.testkey,
    $1.tes,
    $1.testname
)
WHERE NOT EXISTS (
    SELECT 1
    FROM upd
);

This way you can avoid using an expensive cursor, and an even more expensive EXCEPTION clause.

Note that this is basically the same solution as one of the several Erwin provides in his answer (for historical reasons, two questions were merged into one with all their answers). So the warning about concurrency applies to this one as well.

share|improve this answer
    
@Deszo, thanks for the clean approach,will this support concurrency? –  indago Jan 23 '13 at 6:06

Insetad of

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

use

IF COALESCE(tmp_code, 0) = 0 THEN
    ...
END IF;

Note that "tmp_code" is smallint while "COALESCE(tmp_code, 0) = 0" is boolean.

Another bug - function returns boolean but doesn't have return instruction.

If netcen.test.testkey is a primary key you can use exception (39.6.6), especially look at Example 39-2 - it's just what you want to do.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.