| 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: | Whole Thread | Raw Message | 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
| 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? |