Functions returning RECORD
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
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:
Show quoted text
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
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
Show quoted text
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/