2 note about relationship to Erwin's answer and concurrency
source | link

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.

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.

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.

    Post Merged (destination) from dba.stackexchange.com/questions/33337/…
    Post Migrated Here from stackoverflow.com
1
source | link

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.