By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,761 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,761 IT Pros & Developers. It's quick & easy.

function returning a record

P: n/a
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 ma*******@postgresql.org)

Nov 22 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a
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 ma*******@postgresql.org)


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.