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.

Target : What i'm trying to achieve is to restore a partial backup of a database (only certain rows that satisfied certain conditions have been backed up).

Problem : Since between the partial-backup and the restore may pass some time, and certain records could be deleted, I need to perform an Upsert(Update if record exist, Insert if record doesn't exists). This operation in Mysql would have be done with REPLACE, but in PostgreSQL it doesn't exist. After some research I've found a good substitute of it(correct me if i'm wrong) in this function :

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
        RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

The problem with this is that I need to make dynamic the input parameters of the function, and also the UPDATE and the INSERT statements, in order to adapt it for every record and every table i'm going to upsert.

Question :

  • is it possible to integrate plpgsql functions in bash scripts in order to makes them more dynamic?
share|improve this question
1  
This function "upserts" one row at a time in a way that works with concurrent writers. But if you need to mass-merge from a single source, it will perform much better to import into a temp table and reconcile data with UPDATE dest.. FROM temptable WHERE join-condition followed by INSERT INTO dest... SELECT from temptable WHERE NOT EXISTS(...) –  Daniel Vérité 2 days ago

1 Answer 1

http://www.postgresql.org/docs/9.1/static/installation-platform-notes.html

See section 15.7.6.2

Looks like you can import bash into plpgsql. I know SQL or ingres sql is compatible with unix. Can't find anything to suggest that plpgsql is.

HTH

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.