0

I assumed that dblink function were not rolledback, but in the use case below it is.

To summarize: Function1 is a dblink call creating a table. Function2 calls function1. Function2 is defined as RETURNS text missing the RETURNS statement. On execution, function2 executes function1 then returns ERROR : Proccess reached end without return.

The table from dblink is not created. Anyone has an explanation?

Function1:

CREATE OR REPLACE FUNCTION function1 (_user text,_pwd text)
    RETURNS text
AS $BODY$
DECLARE
conn_string text;
BEGIN
conn_string := FORMAT('port = 5432 host=%1$s dbname=%2$s user=%3$s password=%4$s','host','db',_user,_mdp);

DROP TABLE IF EXISTS foo;
EXECUTE FORMAT('CREATE TABLE foo AS
               (SELECT * FROM dblink(''%1$s'',''SELECT * FROM bar'') 
               AS t(id int, _text character varying, geom geometry(point)));',conn_string); 

RETURN 'foo created';

END;
$BODY$;

Function2 :

CREATE OR REPLACE FUNCTION function2(_user text,_pwd text)
    RETURNS text
 
DECLARE

BEGIN

PERFORM function1(_user,_pwd);

END; -- END OF FUNCTION REACHED WITHOUT RETURN
$BODY$;

I understand that function2 is not a working function. What I don't is the fact that function1 (and the dblink within) is rollbacked and would like to understand why.

1 Answer 1

1

The following statement does not create a remote table in the database pointed to by the dblink, as the question seems to imply, but a local table foo initially populated with data from the remote.

EXECUTE FORMAT('CREATE TABLE foo AS
               (SELECT * FROM dblink(''%1$s'',''SELECT * FROM bar'') 
               AS t(id int, _text character varying, geom geometry(point)));',conn_string); 

What is executed on the remote is a simple SELECT. The CREATE TABLE is executed locally as part of the current transaction, and it gets rolled back if the transaction fails later.

1
  • Indeed, you're right. I was confusing things with dblink_exec (in a totally different use case). Thanks for clearing it up.
    – Boodoo
    Apr 13, 2023 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.