function returning a record

From: Pascal Polleunus <ppo(at)beeznest(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: function returning a record
Date: 2004-02-17 11:10:15
Message-ID: 4031F697.8070900@beeznest.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Matthew Lunnon 2004-02-17 11:14:30 summary aggregate information from a second table
Previous Message John Sidney-Woollett 2004-02-17 10:07:03 Re: How do I call multiple functions in one trigger?