upserttce.sql

CREATE EXTENSION IF NOT EXISTS pgsodium;

CREATE TABLE test (
    id bigserial primary key,
    name text,
    secret text
    );

SELECT format('ENCRYPT WITH KEY ID %s', (pgsodium.create_key('aead-det')).id) AS seclabel \gset

SECURITY LABEL FOR pgsodium	ON COLUMN test.secret IS :'seclabel';

CREATE OR REPLACE FUNCTION upsert_test(p_id bigint, p_name text DEFAULT NULL, p_secret text DEFAULT NULL)
    RETURNS test LANGUAGE sql AS
    $$
    INSERT INTO test (id, name, secret) VALUES (p_id, p_name, p_secret)
    ON CONFLICT (id) DO UPDATE
        SET name   = coalesce(p_name, (SELECT name FROM test WHERE id = p_id)),
            secret = coalesce(p_secret, (SELECT decrypted_secret FROM decrypted_test WHERE id = p_id))
    RETURNING *
    $$;