Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2014
    Posts
    8

    Unanswered: dblink - void functions

    Hi

    Sorry for yet another post.

    I'm trying to run a function from within another function using dblink, this is so I can create an autonomous transaction. My understanding is that PostgreSQL doesn't support autonomous transactions and this is the suggested workaround for this.

    I can't seem to work this out and fire a function that returns a void

    I'm very new to dblink so probably doing something very stupid! And if I've missed a trick do let me know!

    my dblink code that sit within the function is as follows
    Code:
    PERFORM dblink_connect('dbconn','dbname=xxx user=xxx password=xxx');  
    PERFORM dblink_exec('dbconn','PERFORM public.usp_sendemailtemplate(''a65885ef-7904-4553-a6da-94041e98ff9c'', ''test'', 1, ''test2'');'); 
    PERFORM dblink_exec('dbconn','commit;');  
    PERFORM dblink_disconnect('dbconn');
    The signature for usp_sendemailtemplate is
    Code:
    CREATE OR REPLACE FUNCTION public.usp_sendemailtemplate(p_apisessionid uuid, p_sessionid CHARACTER VARYING, p_websiteuserid BIGINT, p_templatedesc emailtemplatedesc)
     RETURNS void

  2. #2
    Join Date
    Feb 2014
    Posts
    8
    quick explanation of why I want to do this in case someone can think of a better method!

    Basically, if my function fails I want everything to roll back - which it does - fabulous.

    I also want my function to send an email to the user detailing the issue. In order to do that I call another function which writes a row to an email queue table which gets processed and the email sent. Problem is that this row gets rolled back with the main transaction.

  3. #3
    Join Date
    Feb 2014
    Posts
    8
    Took an awful lot of playing around but sorted it.

    dblink code needs to be like this.
    Code:
    PERFORM * FROM dblink('dbname=' || dblink_database || ' user=' || dblink_username || ' password=' || dblink_password ||'', 'SELECT public.usp_sendemailtemplate(''' || p_apisessionid || ''', ''' || p_xojosessionid || ''', ' || user_websiteuserid || ', ''xxx'')') AS x(x TEXT);
    Nothing really out there on the forums/internet to answer this one so, hopefully, this helps others out.

    The odd bit is the
    Code:
     AS x(x TEXT)
    even though the function returns a void, this only works if I set a text variable to store that void! Don't get that at all but this works!

    (unless someone sees a better way I've missed!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •