Thread: Functions returning RECORD

On Thu, Jan 13, 2005 at 07:58:33PM +0200, 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"

If the function will always return the same row type then create a
composite type with CREATE TYPE and return that instead of RECORD.

> please can someone explain to me how to create a column definition list.

See the documentation for SELECT in the Reference part of the
PostgreSQL documentation; the Examples section shows a query with
a column definition list.  But you won't need a column definition
list if you return a known type instead of RECORD.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Hello

try
select * from GetAccountInfo (100) as (integer, integer, integer);

I don't know types of fields a.Field1, a.Field2, a.Field4. I expect for
example all are integer.

regards
Pavel Stehule

On Thu, 13 Jan 2005, Craig Bryden wrote:

> 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
listis required for functions returning "record" 
>
> please can someone explain to me how to create a column definition list.
>
>
> Thanks
>

On Thu, 13 Jan 2005, 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"

You need to say something like:
select * from GetAccountInfo(100) AS foo(field1 int, field2 int, field4
text);

Where the types match appropriately.

This was very helpfull.

Thank you
Craig


> On Thu, Jan 13, 2005 at 07:58:33PM +0200, 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"
>
> If the function will always return the same row type then create a
> composite type with CREATE TYPE and return that instead of RECORD.
>
>> please can someone explain to me how to create a column definition list.
>
> See the documentation for SELECT in the Reference part of the
> PostgreSQL documentation; the Examples section shows a query with
> a column definition list.  But you won't need a column definition
> list if you return a known type instead of RECORD.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>