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

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

SEO

Functions that return RECORD type