Re: sequence values question

just obtain the next value from the sequence first, then do the insert:

CREATE OR REPLACE FUNCTION insert_row(text) returns text language plpgsql
AS $$
DECLARE
vdesc alias for $1;
new_id INTEGER;
BEGIN
SELECT nextval('sequence_name_here') INTO new_id;
INSERT INTO productos (id, desc) VALUES (new_id, vdesc);
RETURN (new_id) || ',' || vdesc;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;

2005/5/10, mmiranda(at)americatel(dot)com(dot)sv <mmiranda(at)americatel(dot)com(dot)sv>:
>
> Hi, how can i know the values generated by a column of type serial?
> I mean, i have the following table
>
> productos
> (
> id serial,
> desc varchar(50)
> )
>
> select * from productos;
>
> +-----+------------+
> | id | desc |
> +-----+------------+
> | 1 | ecard1 |
> | 2 | ecard2 |
> | 3 | ecard3 |
> | 4 | ecard4 |
> | 5 | ecard5 |
> +-----+------------+
>
> I insert a row using a SP, i want to return the id and desc of the new
> product in the table.
> this is an example of the hypothetical SP
>
> CREATE OR REPLACE FUNCTION insert_row(text) returns text language plpgsql
> AS $$
> DECLARE
> vdesc alias for $1;
> BEGIN
> INSERT INTO productos (desc) VALUES (vdesc);
> RETURN (new id ???) || ',' || vdesc;
> END;
> $$
> LANGUAGE 'plpgsql' VOLATILE;
>
> I know i can get the last value using currval(text), and add 1 to the next
> values, is this the only way?, what if i want to insert several products?,
> should i return a record ?
> thanks
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>