sequence values question
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
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@americatel.com.sv <mmiranda@americatel.com.sv>:
Show quoted text
Hi, how can i know the values generated by a column of type serial?
I mean, i have the following tableproductos
(
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 SPCREATE 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