SEO

#1 (**permalink**) 04-08-2008, 06:50 PM

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

#2 (**permalink**) 04-08-2008, 06:50 PM

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/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

#3 (**permalink**) 04-08-2008, 06:50 PM

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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#4 (**permalink**) 04-08-2008, 06:51 PM

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

#5 (**permalink**) 04-08-2008, 06:51 PM

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/
>

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Functions returning RECORD