calling function - Mailing list pgsql-jdbc

Hi All,

I created a following function which works fine when I use this query in
winsql client to execute it

SELECT * FROM getAccomms('en_GB') AS accomm(id INTEGER, name VARCHAR,
min_child_age SMALLINT, max_child_age SMALLINT, image_id VARCHAR, url
VARCHAR, alt_text VARCHAR);

But using java code it doesn't work fine. It just hangs. After this I close
my java program and again execute the above query using winsql it gives
error

Error: ERROR:  relation with OID 659490 does not exist (State:S1000, Native
Code: 7)

Please tell me what is the problem?

CREATE OR REPLACE FUNCTION getAccomms(VARCHAR) RETURNS SETOF record AS '
DECLARE
    locale ALIAS FOR $1;

    accomms RECORD;
    images RECORD;

    innerDelimiter VARCHAR := ''#'';

    id_var VARCHAR := '''';
    url_var VARCHAR := '''';
    alt_text_var VARCHAR := '''';
BEGIN
    CREATE TABLE temp_accomm (
        id                  INTEGER,
        name                VARCHAR(100),
        min_child_age       SMALLINT,
        max_child_age       SMALLINT,
        image_id            VARCHAR(100),
        url                 VARCHAR(500),
        alt_text            VARCHAR(500)
    );

    FOR accomms IN
        SELECT a.id, atxt.name, a.min_child_age, a.max_child_age
        FROM accomm AS a
        INNER JOIN accomm_text AS atxt ON atxt.accomm_id = a.id
        WHERE atxt.locale = locale
    LOOP
        INSERT INTO temp_accomm
        (id, name, min_child_age, max_child_age)
        VALUES (accomms.id, accomms.name, accomms.min_child_age,
accomms.max_child_age);

        FOR images IN
            SELECT image.id, image.url, image.alt_text
            FROM image
            INNER JOIN accomm_images ON accomm_images.image_id = image.id
            WHERE accomm_images.accomm_id = accomms.id
        LOOP
            id_var := id_var || images.id || innerDelimiter;
            url_var := url_var || images.url || innerDelimiter;
            alt_text_var := alt_text_var || images.alt_text ||
innerDelimiter;
        END LOOP;

        id_var := trim(trailing innerDelimiter from id_var);
        url_var := trim(trailing innerDelimiter from url_var);
        alt_text_var := trim(trailing innerDelimiter from alt_text_var);

        UPDATE temp_accomm
        SET image_id = id_var, url = url_var, alt_text = alt_text_var
        WHERE id = accomms.id;

        id_var := '''';
        url_var := '''';
        alt_text_var := '''';

    END LOOP;

    FOR accomms IN
        SELECT id, name, min_child_age, max_child_age, image_id, url,
alt_text
        FROM temp_accomm
    LOOP
        RETURN NEXT accomms;
    END LOOP;

    DROP TABLE temp_accomm;

    RETURN;
END;
' LANGUAGE 'plpgsql';


________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________