Return to Question

deleted 27 characters in body

Source Link

edited Apr 12, 2023 at 9:36

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

To summarize: Function1 is a dblink call dblink 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
conn_string text;

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.

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

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
conn_string text;

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.

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.

Source Link

asked Apr 12, 2023 at 9:11

Boodoo

Why is dblink nested function rolling back?

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

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
conn_string text;

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.

postgresql dblink