Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 6AA8E9FB28D for ; Tue, 3 Jul 2007 09:25:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 02690-08 for ; Tue, 3 Jul 2007 09:25:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.250]) by postgresql.org (Postfix) with ESMTP id AC76F9FA66C for ; Tue, 3 Jul 2007 09:25:52 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so367766ana for ; Tue, 03 Jul 2007 05:25:51 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=UYKzCeniJixMAT47+oSW+uMMcoZYqscoJlehk8MeUczQtK2hMF18EZFozM9j+WTKWboke4dnrFL4InodSWiNFIxP0y1a/c7LY+ohZe7dSD9vPhFFsk0KgihfzduGmB0bYNf81lS5cNJfdVKG/phNTrTD1lPMzQeSc3cHBDVa/QA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=SiQe0ai6GwT3jg0q2AGK4IqLaBAqIqpX5X/fYAnKdltcpjAxaMi7YBXSP9WD03vUU6BNiirRILzkgnIPAOACFHXQ+aQgJcUlPeeVLHYHcyEju6h2s3UtyzRHFPvGJBJb2c/a45+HubUgj4PtoXU7h2Lm/RNDXzJ5ffJ9L9CgwTc= Received: by 10.100.163.12 with SMTP id l12mr4257878ane.1183465551529; Tue, 03 Jul 2007 05:25:51 -0700 (PDT) Received: by 10.100.194.7 with HTTP; Tue, 3 Jul 2007 05:25:51 -0700 (PDT) Message-ID: <1c23c8e70707030525m59ee79d0r425813cd93131bbc@mail.gmail.com> Date: Tue, 3 Jul 2007 15:25:51 +0300 From: "Loredana Curugiu" To: "Bart Degryse" , "Pavel Stehule" , pgsql-sql@postgresql.org Subject: Re: dblink inside plpgsql function In-Reply-To: <468A36F0.A3DD.0030.0@indicator.be> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_122623_22931991.1183465551451" References: <1c23c8e70707030202n323ee171n20f39cdece5b3618@mail.gmail.com> <162867790707030213h7c81e498p1b5f9d7c9cf1cc9b@mail.gmail.com> <468A36F0.A3DD.0030.0@indicator.be> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/13 X-Sequence-Number: 28748 ------=_Part_122623_22931991.1183465551451 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I created the following function CREATE OR REPLACE FUNCTION getReminderServices( varchar ) RETURNS SETOF reminder_services AS' DECLARE r reminder_services%ROWTYPE; BEGIN SELECT dblink_connect(''dbname=''||$1); FOR r IN SELECT * FROM dblink(''SELECT * FROM reminder_services'') AS columns( uid INT, theme_uid INT, activity_MT_amount INT, activity_min_days INT, activity_max_months INT, inactivity_days INT, limit_reminders INT, limit_months INT, scanning_time TIMESTAMP WITH TIME ZONE, reminder_time TIMESTAMP WITH TIME ZONE, message TEXT) LOOP RETURN NEXT r; END LOOP; SELECT dblink_disconnect($1); RETURN; END; ' LANGUAGE plpgsql; and I get the same errors. I think it is a problem with the dblink because the following function it works fine if I call SELECT * FROM getReminders(). CREATE OR REPLACE FUNCTION getReminders() RETURNS SETOF reminder_services AS' DECLARE r reminder_services%ROWTYPE; BEGIN FOR r IN SELECT * FROM reminder_services LOOP RETURN NEXT r; END LOOP; RETURN; END; ' LANGUAGE plpgsql; ------=_Part_122623_22931991.1183465551451 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I created the following function

CREATE OR REPLACE FUNCTION getReminderServices( varchar ) RETURNS SETOF reminder_services AS'
DECLARE r reminder_services%ROWTYPE;
BEGIN
    SELECT dblink_connect(''dbname=''||$1);
    FOR r IN SELECT * FROM dblink(''SELECT * FROM reminder_services'')
               AS columns( uid INT,
                                     theme_uid INT,
                                     activity_MT_amount INT,
                                     activity_min_days INT,
                                     activity_max_months INT,
                                     inactivity_days INT,
                                     limit_reminders INT,
                                     limit_months INT,
                                     scanning_time TIMESTAMP WITH TIME ZONE,
                                     reminder_time TIMESTAMP WITH TIME ZONE,
                                     message TEXT)
    LOOP
    RETURN NEXT r;
    END LOOP;
    SELECT dblink_disconnect($1);
    RETURN;
END;
' LANGUAGE plpgsql;


and I get the same errors. I think it is a problem with the dblink because
the following function it works fine if I call SELECT * FROM getReminders().

CREATE OR REPLACE FUNCTION getReminders() RETURNS SETOF reminder_services AS'

DECLARE r reminder_services%ROWTYPE;

BEGIN
    FOR r IN SELECT * FROM reminder_services
    LOOP
    RETURN NEXT r;
    END LOOP;
    RETURN;
END;
' LANGUAGE plpgsql;





------=_Part_122623_22931991.1183465551451--