Committing transactions while executing a postgreql Function

by

This can be done using dblink. I showed an example with one insert being committed you will need to add your while loop logic and commit every loop. You can http://www.postgresql.org/docs/9.3/static/contrib-dblink-connect.html

CREATE OR REPLACE FUNCTION log_the_dancing(ip_dance_entry text)
RETURNS INT AS
$BODY$
    DECLARE
    BEGIN
        PERFORM dblink_connect('dblink_trans','dbname=sandbox port=5433 user=postgres');
        PERFORM dblink('dblink_trans','INSERT INTO dance_log(dance_entry) SELECT ' || '''' || ip_dance_entry || '''');
        PERFORM dblink('dblink_trans','COMMIT;');
        PERFORM dblink_disconnect('dblink_trans'); 

        RETURN 0;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

ALTER FUNCTION log_the_dancing(ip_dance_entry text)
  OWNER TO postgres;

BEGIN TRANSACTION;
  select log_the_dancing('The Flamingo');
  select log_the_dancing('Break Dance');
  select log_the_dancing('Cha Cha');
ROLLBACK TRANSACTION;

--Show records committed even though we rolled back outer transaction
select *
from dance_log;

More Related Contents:

  1. Table name as a PostgreSQL function parameter
  2. Define table and column names as arguments in a plpgsql function?
  3. No function matches the given name and argument types
  4. Difference between method and function in Scala
  5. Difference between language sql and language plpgsql in PostgreSQL functions
  6. What is the difference between a ‘closure’ and a ‘lambda’?
  7. How to explain callbacks in plain english? How are they different from calling one function from another function?
  8. Computing the cross product of two vectors in Fortran 90
  9. Non exhaustive pattern in function in GHCi
  10. How is the square root function implemented? [closed]
  11. Static vs class functions/variables in Swift classes?
  12. Whats the difference of functions and methods in Go?
  13. R: 2 functions with the same name in 2 different packages
  14. How to get function parameter lists (so I can drop a function)
  15. Define a recursive function within a function in Go
  16. When is a function too long? [closed]
  17. Two ways of defining functions in Scala. What is the difference?
  18. Kotlin: How can I create a “static” inheritable function?
  19. use onedit() trigger on a specific sheet within google scripts for google sheets
  20. How can I generate a list of function dependencies in MATLAB?
  21. Functions with variable number of input parameters
  22. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  23. MsgBox “” vs MsgBox() in VBScript
  24. Why can’t ‘kotlin.Result’ be used as a return type?
  25. How to evaluate functions in GDB?
  26. MATLAB error: Undefined function or method X for input arguments of type ‘double’ [duplicate]
  27. Bodiless function in Golang
  28. Matlab ode45. How to change a parameter inside it while calling it?
  29. How do you strip the unit from any number in SASS?
  30. In R, what exactly is the problem with having variables with the same name as base R functions?

Leave a Comment