CODEWITHSUNDEEP
sqlpostgresql
MParker
MParker

Reputation: 315

User-defined function with bind params

I am using the Postgres upsert example. I can get it to work as shown in the example but I need to make the function call be dynamic. The function is

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
    UPDATE db SET b = data WHERE a = key;
    IF found THEN
        RETURN;
    END IF;
    BEGIN
        INSERT INTO db(a,b) VALUES (key, data);
        RETURN;
    EXCEPTION WHEN unique_violation THEN
    END;
END LOOP;
END;
$$
LANGUAGE plpgsql;

I can get it to work this way:

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

But I would like to do something like:

SELECT merge_db($1,$2);

Is this possible? I know I can do this by concatenating strings but I would like to prepare my statement and use bind params.

Upvotes: 1

Views: 90

Answers (1)

MParker
MParker

Reputation: 315

Not sure why I didn't think to try this before but here's the answer:

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(INT, TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
    UPDATE db SET b = $2 WHERE a = $1;
    IF found THEN
        RETURN;
    END IF;
    BEGIN
        INSERT INTO db(a,b) VALUES ($1, $2);
        RETURN;
    EXCEPTION WHEN unique_violation THEN
    END;
END LOOP;
END;
$$
LANGUAGE plpgsql;

With that you can use

SELECT merge_db($1, $2)

Upvotes: 1

Related Questions