Re: [GENERAL] function returning a record

[Pascal Polleunus]

Ok, I found the solution :-D

In the function test(), instead of:
SELECT INTO r get_id(''mytable'');
The following must be done:
SELECT INTO r * FROM get_id(''mytable'') AS (id INT, name VARCHAR(50));
/!\ the datatypes must be EXACTLY the same.
For example, specifying CHARACTER VARYING or even VARCHAR(51) instead of 
VARCHAR(50) will lead to the following error:
ERROR:  query-specified return row and actual function return row do not 
match

I hope it will at least help some one...

Pascal


Hi,

I'm trying to return a RECORD from a function, but when I try to use the 
variable I have the following error:
ERROR:  record "r" has no field "id"

Here's an example:

CREATE OR REPLACE FUNCTION test()
RETURNS CHARACTER VARYING AS '
DECLARE r RECORD;
BEGIN
    SELECT INTO r get_id(''mytable'');
    RETURN r.id::TEXT || '' : '' || r.name;
END;' LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION get_id(CHARACTER VARYING)
RETURNS RECORD AS '
DECLARE
    mytable ALIAS FOR $1;
    r RECORD;
BEGIN
    FOR r IN EXECUTE
        ''SELECT id, name FROM '' || mytable || '' WHERE id = 1''
    LOOP
        RAISE NOTICE ''r: %, %'', r.id, r.name;
        RETURN r;
    END LOOP;
    RETURN NULL;
END;' LANGUAGE 'plpgsql';
test=> select test();
NOTICE:  r: 1, ttt
CONTEXT:  PL/pgSQL function "test" line 3 at select into variables
ERROR:  record "r" has no field "id"
CONTEXT:  PL/pgSQL function "test" line 5 at return
Any idea welcomed ;-)

Thanks,
Pascal


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]