Re: 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