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:
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:
Blog post on UPSERT by Depesz.
Constraints and indexes are two entirely different things. A constraint defines what can go in the column and an index creates an internal structure to help performance.
-- (1)
create table TestTable (
ID autoincrement primary key
, F int not null
);
This is part of the table/column definition. Probably the easiest and most common way to restrict a column from null values.
or:
-- (2)
create table TestTable (
ID autoincrement primary key
, F int
, constraint cnnF not null (F)
);
This is a constraint. This will also work but it's really meant for things like making sure a char(1) column only has a Y or an N in it.
or:
-- (3)
create table TestTable (
ID autoincrement primary key
, F int
);
create index idxF on TestTable (F) with disallow null;
This is an index. As a side effect it is disallowing null but really it's purpose is is to speed up your queries.
I hate to say it but lazy isn't going to work. I would put null/not null definitions into the column definition. Then use constraints on columns where you want to restrict what values go into the column, not if it's null or not. Then last but not least add indexes where needed for query performance.
Best Answer
Use CREATE DOMAIN with a CHECK constraint. This works in PostgreSQL 9.1. It's documented to work in at least 8.0+. "A partial workaround is to use domain types as members of composite types."
This INSERT statement should succeed.
But this one should fail.