Functions that return RECORD type
Hi
I come from a MS-SQL background and am trying to figure out what is wrong
with the function below:
****************************************************************************
*************
CREATE OR REPLACE FUNCTION GetAccountInfo (p_AccID int)
RETURNS record
AS
$$
DECLARE
r_Return record;
BEGIN
SELECT a.Field1, a.Field2, a.Field4
INTO r_Return
FROM Account
WHERE a.AccID = p_AccID;
RETURN r_Return;
END;
$$
language 'plpgsql';
****************************************************************************
*************
When I run select * from GetAccountInfo (100) I get the following
error message: ERROR: a column definition list is required for functions
returning "record"
please can someone explain to me how to create a column definition list.
Thanks
Craig Bryden wrote:
When I run select * from GetAccountInfo (100) I get the following
error message: ERROR: a column definition list is required for functions
returning "record"please can someone explain to me how to create a column definition list.
CREATE FUNCTION foo() RETURNS SETOF RECORD AS
'SELECT 1::int,2::int,''A''::text;'
LANGUAGE sql;
SELECT * FROM foo() AS (a int, b int, c text);
a | b | c
---+---+---
1 | 2 | A
(1 row)
The other way (which I prefer) is to define a type and change the
function definition:
CREATE TYPE foo_res_type AS (a int, b int, c text);
CREATE FUNCTION foo() RETURNS SETOF foo_res_type ...
--
Richard Huxton
Archonet Ltd