Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013
    Posts
    3

    Red face Unanswered: dblink error when calling a function that returns setof..

    I have this function that works great if call from the db your connected to
    CREATE TYPE v_over_c AS (linkid INTEGER,from_sec INTEGER,to_sec INTEGER,volume INTEGER,v_over_c numeric);

    CREATE OR REPLACE FUNCTION compute_v_over_c(aggregation INTEGER,start_time INTEGER,end_time INTEGER)

    returns setof v_over_c AS
    $$
    DECLARE
    r v_over_c%ROWTYPE;

    i INTEGER:=$2;

    BEGIN

    LOOP
    EXIT WHEN i=$3;
    FOR r IN SELECT a.linkid,i AS from_sec,i+$1 AS to_sec,b.flow-a.flow ,ROUND(CAST((b.flow-a.flow)/(capacity*lanes*$1/3600) AS numeric),2) AS v_over_c FROM links_flow_out(i) a,links_flow_out(i+$1) b, linkdetails c
    WHERE c.id=a.linkid AND b.linkid=a.linkid LOOP
    RETURN next r;
    END LOOP;
    i:=i+$1;
    END LOOP;
    RETURN;
    END;
    $$
    language plpgsql;
    when I call this function like:
    SELECT * FROM compute_v_over_c (300,1800,3600); it returns a set of v_over_c just fine
    but when I call it from a remote db so I can join simular results like this:
    SELECT * dblink('mylink', 'SELECT * FROM compute_v_over_c (300,1800,3600)'); mylink being a connection to another db
    I get this
    ERROR: function returning record called in context that cannot accept type record
    any ideas?

  2. #2
    Join Date
    Apr 2013
    Posts
    3
    Figured it out. Needed to use a cursor and then fetch to get the records as they are returned instead of just the query call.

Posting Permissions

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