Thread: 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