Custom Field for a table row returned from stored procedure

In response to Yan Cheng Cheok :

I have a "lot" table with 2 columns, with one of the column is current timestamp.

I try to return another custom fields, which its calculation is based on timestamp.

(For simplicity, I include only 1 field in following example)

CREATE OR REPLACE FUNCTION create_lot(text)
RETURNS lot AS
$BODY$DECLARE
configurationFile ALIAS FOR $1;
_lot lot;
BEGIN
INSERT INTO lot(configuration_file)
VALUES(configurationFile) RETURNING *, extract(epoch from timestamp) as timestampex INTO _lot;
return _lot;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION create_lot(text) OWNER TO postgres;

I expect the table returns two field for a single row. 1 is configuration_file, 2nd is timestamp, 3rd is timestampex.

^^^^^^ ^^^^^^^^^^^^^^^^^

I think, you expected the function returns 3 fields, right?

However, I only able to retrieve configuration_file and timestamp. How can I obtain timestampex too?

You have defined your function "RETURNS lot", and the data-type "lot"
contains only 2 fields.

I think, you have to rewrite your function, simple example:

test=# create table lot (a int, b int);
CREATE TABLE
test=*# create or replace function insert_lot(in _a int, in _b int, out x int,out y int,out z int) as $$begin
insert into lot values (_a, _b);
x:=_a;
y:=_b;
z:=_a+_b;
return;
end;$$ language plpgsql;
CREATE FUNCTION
test=*# select * from insert_lot(2,5);
x | y | z
---+---+---
2 | 5 | 7
(1 row)

HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99