Обсуждение: function returning a record

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

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 majordomo@postgresql.org)

Thanks Pascal. You just helped me :o)

On Tuesday 17 February 2004 06:39 am, Pascal Polleunus saith:
> I hope it will at least help some one...
>
> Pascal

--
 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com